Bulk Insert Task in SSIS

12
4185

Description – Bulk insert task helps us to import text files into a MS SQL Server table. You cannot validate, clean or transform data with this task. Let’s get started and get into this concept by taking a simple example.

Example Scenario :- We will be having a Text file with some data in it (as I am having list of Dota players in my text file). Then, we will create one package (I have created a package named cK_Bulk_Insert_Task) and then with the help of Bulk insert task, the data in text file will be inserted into SQL Server Table.

STEPS TO FOLLOW:-
Step 1. Open Business Intelligence Development Studio (BIDS) by any way you prefer. Create a new package and give it any desired name, say cK_Bulk_Insert_Task.dtsx.

Step 2. Open SQL Server Management Studio (SSMS). Create a table for storing Dota Players information in your working database (as in this example, Chander is my working database and I am creating a table with a name dbo.Dota_players with the following fields).
Create table dbo.Dota_player
(
Name nvarchar(50) not null,
Age nchar(10) not null,
Sex nvarchar(10) not null,
Skill nvarchar (50) not null,
Location nvarchar(50) not null
)

Step 3. Back in your new package, go to control flow items in toolbox and drag the Bulk Insert Task onto the Control Flow design pane. Notice that the task has a red icon on it, indicating that it hasn’t been configured yet.

Now, double-click the Bulk insert task to open the editor to configure it. In the General tab specify the following fields as:-

  • For the Name option – write Dota Players and
  • For the Description – write Loads Dota Players Information from a flat file.

General Tab for Bulk insert task

Step 4. Click on the Connection tab and proceed as:-

  • From the Connection dropdown box, select <NEW CONNECTION ..>. This will open the Configure OLE DB Connection Manager dialog. Now, we will create a connection to the database. Click New to add a new Connection Manager. For the Server Name option – select local host and for the Database – select database name.Connection Manager for Bulk insert task

Step 5. Click OK to go back to the previous screen and click OK again to return to the Bulk Insert Task Editor. You’ll now see that the Connection Manager you just created has been transposed into the Connection dropdown box.

STEP 6. Now we need to define the destination. For the Destination Table option, Select the [chander].[dbo].[Dota_players] table.

Destination Connection for Bulk insert task

NOTE: – The Bulk Insert Task 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.

STEP 7. 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 of Dota Players, columns are separated by a comma.

STEP 8. For the File option under Source Connection, again select <New connection …> to create a new Connection Manager. This will open the File Connection Manager Editor. For the Usage Type, select Existing File and then Browse to DotaPlayers.txt for the File option. Click OK to return to the editor.

Collection Tab for Bulk insert task

NOTE: – If you had a column header and needed to skip it, you would go to the Options tab and change the First Row option to 2. This would start the import process on the second row, instead of the first, which is the default.Execution

STEP 9. You should be able to execute the package now. When it runs, the table will be populated with all the Dota players information from the import file. Let’s verify this by selecting all the rows from the Dota Players table from MS SQL Server.

Database

With this our implementation of Bulk Insert Task is finished under the Control flow section and in further posts we will go into the concept of Data Flow. I hope this example helps you to clarify your concept of Bulk Insert task. If you are having any queries regarding this post, feel free to post comment.

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 9.7/10 (32 votes cast)
VN:F [1.9.22_1171]
Rating: +27 (from 29 votes)
Bulk Insert Task in SSIS, 9.7 out of 10 based on 32 ratings