October 29, 2009


As we have seen SSIS Data Flow TASK in previous article, today I am going to explain SSIS BULK INSERT TASK.

The Bulk Insert task provides an efficient way to copy large amounts of data into a SQL Server table or view. Let's say company is using Online Product Marketing and every day they have to update their databases with the latest information provided by their suppliers in Tab Separated/Comma Separated file. Here BULK INSERT provides efficient way.

We can move data to SQL SERVER Table or View by BULK INSERT Task.

Let see How to configure SSIS BULK INSERT Task:

1. Drag Control: Drag and drop, BULK INSERT TASK from Toolbox to Design surface:

Bulk Insert 1
2. Configure connection: Right click on Task and click on Edit

Specify Destination Connection: Specify the OLE DB connection manager to connect to the destination SQL Server database and the table or view into which data is inserted.

NOTE: The Bulk Insert task supports only OLE DB connections for the destination database.

Define Format:
We need to Define the format that is used by the Bulk Insert task, either by using a format file or by defining the column and row delimiters of the source data. If using a format file, specify the File connection manager to access the format file. We need to specify Row Delimiter and Column Delimiter, as per our requirement.

Specify Source Connection: Finally, we need to specify Flat or File Connection Manager to access the source file. BULK INSERT Task is process this file as per defined format on previous step.

Bulk Insert SSIS Task 2

3. Configure Options: SSIS BULK INSERT Task also provides to specify some options which needs to perform when BULK TASK Insert some data to destination Table/View, like: Check Constraint, Keep Nulls, Enable Identity Insert, Table Lock, Fire Triggers.

Bulk Insert SSIS Task 3

That's it. Now execute the package, you find that your Table/view is populated with data from provided text file.

We can say that Data Flow Task is advanced version of BULK INSERT TASK, which provides more features and more scalable.

The Bulk Insert task can transfer data only from a text file into a SQL Server table or view. To use the Bulk Insert task to transfer data from other database management systems (DBMSs), you must export the data from the source to a text file and then import the data from the text file into a SQL Server table or view.

With Data Flow Task, we can import/export data from any database, we don't need to convert it to text and then import.

So, we can use either of these as per our requirements.

Let me know if you have  any questions.

Reference: SQL SERVER Books Online, Tejas Shah (http://www.SQLYoga.com)

1 comment:


    I want to know the best way to migrate tables from Oracle 9i
    to SQL Server '08 R2 as a cutover i.e. NO updates, no CDC (Change
    Data Capture) with Attunity. Just SELECT INTO the SQL Server tables,
    which DO NOT yet exist and shutdown the Oracle server for good.

    I do not see any sample SSIS pkgs which do a SELECT INTO where
    tables on the SQL Server do not yet exist.

    Thank you,