Introduction:
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.
Solution:
• 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.
