How to use bulk insert task in SSIS

Hello friends, in recent past I only talked about T-SQL related topics so I thought to pick a different topic.In this article we will learn how to use bulk insert task in SSIS. Bulk insert task is used to import big data files (excel or text) into SQL server with great efficiency. This is the faster way of importing data into SQL Server. First of all we will create a table in database in which we want to import the data from the text file using bulk insert task.

Create Table Emp_Detail(Eid int,Name varchar(50),Dept varchar(20))

By running the above query, we will create the “Emp_Detail” table in the database.

Now we will open BIDS(business intelligence development studio) tool to create package where we will use the bulk insert task. We will drag and drop the bulk insert task in the control flow window as shown in the below screenshot.

We will right click on the bulk insert task and click on “Edit” menu that will bring a new screen in front of us as shown in the below screenshot.

If you look at the above screenshot, there are four tabs (General, Connection, Options and Expressions) at the upper left side of the window, if we click on each tab a different screen will appear. Currently we have selected the “General” tab where I have put the name of the bulk insert task as “Import Employee Detail”. Now we will select the “Connection” tab where we will define the source and destination connection. Once we click on the “Connection" tab, we will see below screen.

In the above screenshot, I have encircled two areas one is destination connection and 2^nd one is for source connection. In destination connection we will specify the SQL table in which we want to import the data and for source connection we will provide the text file path from which file we are importing the data. I have already explained how to specify the destination connection and source connection in my earlier articles hence I am not going into those details. I have specified source and destination connections and after that current screen will look like as the below screenshot.

In destination table, we can see the name of the table which we created earlier and in source, we can see that “Emp.txt” file, which we are going to import. In “ColumnDelimiter”, I have specified “Comma {,}” as I am bifurcating the column values using “Comma”. Below is the screenshot of the “Emp.txt” file.

Now we will click on “OK” button and we are ready to run this package. We will press “F5” key to run this package, If  bulk insert task turns into green color, it means our package has executed successfully.

Lets check the “Emp_Detail” table to see if all the records have been transferred from the text file to database or not.

We can see in the above screenshot that all the rows have been transferred to the database successfully. I hope you will find this article useful , please let me know if you have queries about this article.