Friday, 29 May 2015

Load data from multiple Excel sheets to Database in SSIS

In this article, I will explain How to Load data from multiple Excel sheets (5 Sheets) in one Excel file into single table by using SSIS.
Let us consider the following MS-Excel spreadsheet with data in 5 different sheets. Each sheet contains 3 columns of data.

• First to create a SQL Table which will store excel sheets data.
CREATE TABLE [OLE DB Destination] (
[productname] nvarchar(255),
[sale] float,
[saledate] datetime
• Now create a variable @Sheetname of string type and assign the value Jan2010$. Ensure the variable value ends with “$”.

• Create two tasks in control flow as follows.
• Double click the Foreach loop container, go to the Collection tab.
• Select “Foreach ADO.Net Schema Rowset Enumerator” in Enumerator Configuration list.

• From the drop down of Connection, select “New connection...”

• Click on the “New” button of the Configure ADO.Net Connection Manager.
• You get another popup window Connection Manager. In Connection Manager list we have selected “Microsoft Jet 4.0 OLE DB Provider” from “.Net Providers for OleDb” drop down.

• Browse the file which has multiple Excel sheets from which we will load data.

• Go to the "All" tab, go to the "Advanced" tab, and set the Value as "Excel 8.0" for "Extended Properties".

• And press Test the connection. You got the message “Test Connection succeeded”.
• In the Collection tab, select the Schema as "Tables". This will configure the Foreach loop container.

• Added index value “2” to the variable of @User::SheetName in Foreach loop container.

• In DataFlow Task, add an “Excel Source” and double click on Source connection to Configure the excel connection manager as follows

• Select “Table name or view name variable” in Data Access mode, and assign
the variable value to variable name.

• Added “OLEDB Destination” and assign the SQL table to it.
Finally the Data Flow Task as shown below.

Note: all Excel sheets in the source and SQL Table in destination must have the same structure. 

Let me know what you think about this article. 


  1. nice info can find more

  2. nice info can find more