In this article, we will load data present in multiple
Excel sheets to an Excel sheet or any other destination. For example, we have a
source as an Excel, which has data about an organisation. Suppose this Excel
sheet is storing data in different sheets. Like we have sheets as
"Accounts", "IT", "Admin", etc.
Now our purpose is to load this into the destination. How can
we do this using SSIS?
Using the code
- Create a package in SSIS and rename it "LoadingExcelSheets".
- Now create a variable
varSheetName of string type and assign
the value "IT$". Ensure the variable name ends with "$".
- This will help recognize the sheets in our Excel.
- Now add a
ForEach loop container and rename it as "
- As we will be loading data from multiple sheets, we need to configure our
Foreach loop container as below.
- Double click the
Foreach loop container, go to the Collection tab.
- In the Collection tab, select "Foreach ADO.NET Schema Rowset Enumerator".
- When we add this enumerator, we get many different configurations in the Enumerator Configuration.
- Now select Connection from the Enumerator Configuration.
- From the dropdown, select New connection.
- Click on the New button of the Configure ADO.NET Connection Manager.
- This will popup another window, which is nothing but the connection manager window.
- Now, in the provider dropdown, expand ".NET Providers for OleDb"
and select "Microsoft Jet 4.0 OLE DB Provider".
- Refer the below screenshots.
- 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".
- Test the connection.
- In the Collection tab, select the Schema as "Tables". This will configure the
Foreach loop container.
- Now add a Data flow task and rename it
- In the data flow, add an "Excel Source" and rename it
- Double click on the DFT, add a new connection manager for the Excel, and rename it
- From "Data_Access_Mode", select "Table name or view name variable" as shown in the below image.
- Now from the "Variable_name" dropdown, select the variable which we created as "
- This will help the
foreach loop to loop through all the sheets in Excel.
- Now configure the destination as Excel.
- This will configure the DFT.
- Now execute the package, this package will load all the data from our source Excel sheets to the destination Excel sheet.
- Note here, data is loaded into one Excel sheet in the destination...
Please note, the structure of the Excel sheets must be same. I.e., if the
first sheet has the structure:
ID Age Salary
1 31 5000
all other Excel sheets in the source must have the same structure.