Execute SQL and Bulk Insert Tasks in SSIS

The Connection Page of Bulk Insert Task should be
configured with the below details.

Destination Connection
Connection	SQL Server Instance Name
DestinationTable	[SSISExample].[dbo].[Employee] (Choose table name from Drop-down list)
Format
Format	Specify (This means you are specify the format within the SSIS package configuration)
RowDelimiter	{CR}{LF} (Specify the row delimiter in the input data, you can choose the value from the Drop-down list)
ColumnDelimiter	Comma {,} (Specify the column delimiter in the input data, you can choose the value from the Drop-down list)
Source Connection
File	BulkInsertData (Specify the text file to use as the data source, you can choose the value from the Drop-down list)

8.   Switch to the Options page within the Bulk Insert Task Editor. Here
you are required to configure all the properties which will be used by the SSIS
package to import the text files data into a SQL Server Table. 

                         

The choices which are available in this page have a significant
impact on the way in which the Bulk Insert Task is processed:

Advanced Options:

·        
CodePage: – This
specifies the code page of the data which is available in the text file. The
default value is RAW which speeds up the processing by skipping
transactions from one code page to another code page.

·        
DataFileType: – This specifies the data type which can be used in the load
operation. There are four possible values – CHAR which indicates
standard character format, NATIVE option provides you superior performance
during the data load, WIDECHAR can be used to accommodate UNICODE
characters; WIDENATIVE option allows UNICODE characters in CHAR, VARCHAR
and TEXT columns.

·        
BatchSize: – Specifics
the number of rows in a batch. Each batch is copied to the server as one
transaction. SQL Server commits or rolls back, in the case of failure, the
transaction for every batch. By default, all the data in the specified data
file is one batch.

·        
LastRow: – Specifies
the row at which the insertion of input data stops. The default is 0,
indicating the last row in the specified data file.

·        
FirstRow: – Specified
the row at which the inserting of input data begins. The default is 1,
indicating the first row in the specified data file.

Options:

Options: – Specifies the bulk insert
options to be use when inserting data into the table. The different options are
Check Constraint, Keep Nulls, Enable Identity Insert, Table Lock and Fire
Triggers.

SortedData: – Specifies the columns
on which to sort the data. It is advised to keep the value as FALSE for optimum
performance.

MaxErrors: – Specifies the maximum
number of errors that can take place before terminating the SSIS package.

9.   Connect Execute SQL Task and Bulk Insert Task together as shown in
the snippet below.

10.                           
Finally, execute the package by right clicking the BulkInsertTaskExample.dtsx package from
the solution explorer and select the Execute Package option from the drop down
list. Once the package has successfully executed you will be able to see the
below screen within the control flow designer.

11.                           
Once the package has executed successfully execute the below TSQL
to verify the data in Employee table of SSISExample database.

Use SSISExample

GO

Select
* from dbo.Employee

GO

Tips to Improve Bulk Insert
Task Performance

1.   It is advised not to perform any sort operation while loading the
data from text files using the Bulk Insert Task.

2.   Data will be loaded faster when there are no indexes created on
the staging table.

3.   If the text files are available on the same server as the SQL Server
Database, that data will be loaded much faster as opposed as the data does not
have to move over a network.

Conclusion

In this article you have seen
how to configure and use and combine the Execute SQL and Bulk Insert tasks which are available in SQL Server 2005 Integration Services and later versions.

]]>