How to import data from excel to SQL Server( Part 2 Using BIDS)

Good Evening friends.. Today I am going to walk you through with steps to import data from excel to database table using BIDS(business intelligence development studio) or in other words you can say using SSIS package. In the start menu we will find "SQL Server Business Intelligence Development Studio" option, I have encircled it with blue marker. We will click on this option that will open the BIDS.

Once BIDS(Business Intelligence Development Studio) is opened, we will follow File-->New-->Project, as shown in the below image.

After clicking on "Project" option, a new dialogue box will be opened as shown in the below image.There we will select "Integration Service project" that is encircled by blue color. Now I have given name to this project "BIDSEXAMPLE" which is encircled by red color.

Now, we will click on "OK" button, Which will bring us on the below mentioned user interface.

Any SSIS package contains four tabs, which are following.
1)Control Flow
2)Data Flow
3)Event Handlers
4)Package Explorer
Note :- Any SSIS package will contain at least one data flow task. We will select the data flow task from the tool bar as shown in the below picture and drag it to control flow window.

In the below picture, one data flow task is kept in the control flow area.

I have renamed it with "Import Data" as shown in the below picture.

Now we will double click on data flow task that will bring us on data flow area as shown in the below picture.

As we want to import data from Excel to SQL Server table, we will need Excel source.
We will select the "Excel Source" from the tool bar and drag it to data flow area as shown in the below image.

Now, we will right click on the excel source task and click on the "Edit" menu, which will bring a new window as shown in the below image.

Click on the "New" button  to create a connection with the Excel source. Which will bring a new screen as shown in the below image.

We will click on the "Browse" button and locate the excel file which we want to import. Click on "OK" button which is shown in the above image. Now we will select the "Sheet1$" as all the which we want to import is in the sheet1$.

At the left side in the "Excel source editor" , there is a label named "Columns", we will select Columns that will show the column names which are present in the excel sheet(Eid,Ename,Basic_Sal,HRA).

Click on "ok" button that will bring the below screen.

As we want to import the data into SQL Server table, we will need a "OLEDB Destination" You can see that task which is encircled with red color. We will select the "OLEDB Destination" and drag it to Data flow task area as shown in the below picture.

In the above image we can see two arrows coming out from the excel source, we will hold the green arrow and pull it towards "OLEDB Destination" and link it to it(As shown in the below image).

Now, We will right click on the "OLEDB Destination" and click on edit option that will bring us to the below window.

To create connection with SQL Server, we will click on "New" button which will bring us to the below screen.

In the new window, we will click on the "New" button as shown in the above picture at the bottom side.Clicking on "New" button will further bring us to a new window as shown in the below image.

We will provide the server name and select the appropriate Database in the above window and click on the "Test Connection" button to test the connection.Once the connection is passed, we will select the table name in which we want to import the data from excel sheet(See table name, encircled with blue color).

Now, we need to Map the excel columns to the database table's columns, for that purpose, we will select the mappings option encircled with red color in the above image. If the columns names in the excel are same as columns names in database table, SQL Server automatically maps these columns. Otherwise we need to map the columns manually. In our example columns names are same.

Now we will click on "OK" button and run this package by pressing "F5" key. We are getting below error message  that "Excel connection manager is not supported in 64 it version SSIS.

To overcome this problem we will go to "Project" menu then click on "BIDSEXAMPLE Properties" which will bring us on a new window.

In the below window, we will select "Debugging" option that is encircled by red color.
There is option "Run64BitRuntime" where we can set it to "False" and click on "OK" button.
Now press "F5" button and package will be run successfully.

I hope after reading this article you will be able to import data from excel to SQL Server table using BIDS.Feel free to ask any question  if you have.