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.

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |