Bulk Insert Task in SSIS

The Bulk Insert task in SSIS can transfer data only from a text file into a SQL Server table or view, which is similar to Bulk Insert in SQL Server. If the destination table or view already contains data, the new data is appended to the existing data when the SSIS Bulk Insert task runs. If you want to replace the data, run an Execute SQL task that runs a DELETE or TRUNCATE statement before you run the Bulk Insert task.

For instance, We are working with stock market data, and every day we are getting billions of data in .csv format (Comma Separated Values). Our task is to copy data inside this .csv file to SQL database table every day. We usually have two approaches to do the same in SSIS

Drag and drop the data flow task and inside the data flow drag and drop flat file source and OLE DB destinations and copy the data. This approach is useful if we want to perform any SSIS transformations.
Use the SSIS Bulk Insert Task. This approach is more powerful compared to the previous one because internally, Bulk Insert Task uses Bulk Copy (BCP) operation (Which is very fast in SQL Server).

Available Options inside the Bulk Insert task in SSIS

Click on the Options tab inside the Bulk Insert Task will give the following options

CodePage: Specify the code page of the data in the data file. Generally used for other languages.
DataFileType: Specify the data-type value to use in the load operation.
BatchSize: Specify the number of rows in a batch. The default is the entire data file. If you set BatchSize to zero, the data loaded in a single batch. For instance, If we set the batch size as 100, then each batch acts as one transaction, and if the task fails after some time, then successfully loaded batches will not be rollback.
LastRow: Specify the last row to copy.
FirstRow: Specify the first row from which copying starts.
SortedData: Specify the ORDER BY clause in the bulk insert statement. The default is false.
MaxErrors: Specify the maximum number of errors that can occur before the Bulk insert operation canceled. A value of 0 indicates that an infinite number of errors are allowed.

Options

TermDefinition

Options

Term	Definition
Check constraints	Checks the column data.
Keep nulls	Select to retain null values during the bulk insert operation, instead of inserting any default values for empty columns.
Enable identity insert	Select to insert existing values into an identity column.
Table lock	Select to lock the table during the bulk insert.
Fire triggers	Select to fire any insert, update, or delete triggers on the table.

Bulk Insert Task in SSIS Example

In this SSIS bulk insert task example, we are going to copy the text file present in the local drive to the SQL Server destination.

We have a flat-file called GEOGRAPHY.txt contains 1000000 Rows. Our task is to insert all the rows using the Bulk Insert Task in SSIS.

Drag and Drop the SSIS Bulk Insert Task from the toolbox to Control flow region

Double click on Bulk Insert Task to configure the source and destination connection strings. In the General tab, we can change the Name and description

Now click on the connection tab to set the source and destination connections. Firstly, we have to configure the source connection so select the file option

NOTE: We must provide the credentials to access the text file present in the local or remote.

By clicking on New Connection, we can select the source text file we want to use. Click on the Browse button to select the text file. Here we are choosing the GEOGRAPHY text file of size 92, 562 KB.

Click ok to finish SSIS Bulk Insert Task source configurations

The Row Delimiter property option will be {CR}{LF} (a carriage return). And the Column Delimiter property will set to Comma{‘} as in our flat file, columns separated by a comma.

Now let us configure the destination connection by click on <New Connection>. Connection means selecting the SQL server database name. Here we are choosing SSIS Tutorials Database as the target database.

Now, configure the SSIS Bulk Insert Task destination table by selecting it from the list. Here We are selecting GEOGRAPHY table as the destination table

TIP: The destination table should be pre-designed in the SQL server.

NOTE: The Bulk Insert Task in SSIS does not log error-causing rows. If you want bad records to write to an error file or table, it’s better to use the Data Flow Task.

Click on the “Options”

We already explained the properties of each option in the starting only. For the time being, We are leaving default settings, but for the First Row, we changed the value to 2. Because you can observe the data that, We have column names in the first row.

Click ok to finish configuring Bulk Insert Task in SSIS and run the package

Let’s open the SQL Server Management Studio and write the following T-SQL command and check the result

SELECT [CountryRegionCode] AS [Country Code]
      ,[EnglishCountryRegionName] AS [Country]
      ,[StateProvinceCode] AS [State Code]
      ,[StateProvinceName] AS [State]
      ,[City]
      ,[PostalCode]
      ,[UnitPrice]
      ,[ExtendedAmount] AS [ExtAmount]
      ,[ProductStandardCost] AS [StandardCost]
      ,[TotalProductCost] AS [ProductCost]
      ,[SalesAmount] AS [Sales]
      ,[TaxAmt]
  FROM [SSIS Tutorials].[dbo].[DimGeography]

OUTPUT

Points to Remember in SSIS bulk insert task

The Bulk Insert task in SSIS can transfer data only from a text file into a SQL Server Table or SQL Server View.
Bulk Insert Task supports the Flat file Connection manager to select the text file.
The Bulk Insert Task in SSIS only supports OLE DB Connection Manager in SSIS for the destination database.
Destination table must exist before it is using in the Bulk Insert Task
Don’t forget to change the First Row option to 2, if you have your column names in the first row of a text file.
It is always good practice to set the batch size to insert a large amount of data.