SSIS: Bulk Insert Task

Bulk Insert Task:

Bulk Insert task is used to copy the large volume of data from flat file to SQL Server destination. This task can load data into a table by using the BULK INSERT SQL command.

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

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 DBMS, 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

Example: 

Consider a scenario where we have large flat files in a folder and we want to transfer data from flat files to SQL server. The size of flat file is very large and we want to transfer data using traditional data flow task, it might affect performance of the package. Using bulk insert task we can load the data into destination with performance improvement but here we cannot transform the data in between the data flow as we can do in data flow task.

Create the following table in SQL Server Database:

CREATE Table Address
(
AddressID INT,
AddressLine1 VARCHAR(100),
City VARCHAR(20),
StateProvinceID INT,
PostalCode INT
)

Drag and drop, BULK INSERT TASK from Toolbox to Design surface. Right click on Task and click on Edit.

 

 

 

 

 

 

Define Format: We need to Define the format that is used by the Bulk Insert task 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.

Specify Source Connection: Finally, we need to specify Flat or File Connection Manager to access the source file.

 

 

 

 

 

 

 

 

 

 

 

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.