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. Normally we have 2 approaches to do the same
- 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 very useful if we want to perform any transformations.
- Use the SSIS Bulk Insert Task. This approach is more powerful compared to 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. Normally 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 is loaded in a single batch. For instance, If we set the batch size as 100 then each batch acts as 1 transaction and if the task fails after sometime 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 is canceled. A value of 0 indicates that an infinite number of errors are allowed.
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 Bulk Insert Task in SSIS.
Drag and Drop the SSIS Bulk Insert Task from 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 configure 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 Browse button to select the text file. Here we are selecting the GEOGRAPHY text file of size 92, 562 KB.
Click ok to finish source configurations
The Row Delimiter property option will be {CR}{LF} (a carriage return) and the Column Delimiter property will be set to Comma{‘} as in our flat file, columns are separated by a comma.
Now let us configure the destination connection by click on <New Connection>. Connection means selecting the SQL server database name and here we are selecting SSIS Tutorials Data base as target data base.
Now, configure the destination table by selecting it from the list. Here We are selecting GEOGRAPHY table as destination table
TIP: Destination table should be pre designed in SQL server.
NOTE: The Bulk Insert Task in SSIS does not log error-causing rows. If you want bad records to be written 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]
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.
- The Bulk insert Task supports 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 large amount of data.
Thank you for Visiting Our Blog