SSIS ForEach Loop to load Data from specific Sheets of an Excel Workbook to different SQL Server Tables

SSIS For Each Loop Container to Loop through specific Sheets of an Excel and load data to different SQL Server Destinations
Scenario :
Suppose we have an Excel Workbook(xlsx,2016 version) with multiple Sheets having the Sales data by Product and Service, as follows :

Source:
T:\My_Tech_Lab\SSIS_Lab\SSIS_Proj1\Source

From this Workbook, we needs to Load the data only from the Tabs "Product" , "Service" ; and other tabs need to Skip.

Destination:

The Data from the above Source tabs, we need to load to below SQL Server Destination tables :

We need to make sure that data from tab "Product$" should go to the table "Tbl_SalesByProd" and data from tab "Service$" should go to the table "Tbl_SalesBySvc"

--------------------------------------------------------------------------------------------------------------

SSIS Package Design :

Now we need to design the package as above based on scenario as follows :

Phase- 1 : Creation of the Variables Needed :

Create a Variable to store the Sheet Names of the Workbook and assign one existing Sheet Name as a Value. This value will change in the run time.

Phase- 2 : Creation of the Connections to Database, Excel Source, and an ADO.Net OLE DB Connection for Excel:

here, db_TAMATAM - The connection to the SQL Server DB.

SalesData.xlsx - The connection Excel Source data.

OleDb_SalesData.xlsx :

This is a ADO.Net OleDb Connection to the Excel Source to read the Tabs/Sheets of Workbook

This connection is very important to loop through each Sheet/Tab of the Workbook by ForEach Loop using the "Foreach ADO.NET Schema Rowset Enumerator"

This ADO.NET connection can be created as follows:

Select New ADO.NET Connection from Connection Mangers block

Next select the ".Net Prodividers for OleDB\Microsoft Office 12.0 Access Database Engine OLE DB Provider"

Next Select the Excel Source File 

Next set the Extended Properties of the OLEDB Connection String as "Excel 12.0 XML;HDR=YES"

HDR=YES means the file has Headers.

Note :

If you are connecting to Microsoft Office Excel Data, add the appropriate Extended Properties of the OLEDB Connection String based on the Excel File type, as follows :

Phase-3 : Add the ForEach Loop Container to Control Flow and Set the Properties:

Enumerator : Foreach ADO.NET Schema Rowset Enumerator

Connection : The ADO.NET Connetion(OleDb_SalesData.xlsx)

Schema : Tables

Next do the Variable Mapping "v_SheetName" to Store the Sheet Name dynamically.

Variable :  User:: v_SheetName

Index : 2

Make sure to select Index as "2" to read the Tabs/Sheets of Excel by ForEach Loop.

Phase- 4 : Add the Expression Task to validate the Sheets names which passed by the ForEach Loop:

Expression : 

@[User::v_SheetName]=="Product$"||@[User::v_SheetName]=="Service$"

Add the same Expression to the Precedence Constraint as well.

Expression : 

@[User::v_SheetName]=="Product$"||@[User::v_SheetName]=="Service$"

Evaluation Expression : Expression and Constraint

Phase- 5 : Add the Data Flow Task and define the flow:

Excel Source :

Add the Excel Source and set the properties as follows:

Data Access Mode : Table Name or View Name Variable

Variable Name : User::v_SheetName

This Variable value is Passed by the ForEach Loop to the Data Flow > Excel Source

Next Add the Data Conversion if needed to Convert the Source Data

Conditional Split :

Next Add the Conditional Split to route the Source Data to the different destinations. 

Ouput Name :                Conditon :

Product_Data                 @[User::v_SheetName] == "Product$"

Service_Data                 @[User::v_SheetName] == "Service$"

The data from Sheet "Product$" will go to the table "Tbl_SalesByProd" and the data from Sheet "Service$" will go to the table "Tbl_SalesBySvc".

Final Data Flow :

-------------------------------------------------------------------------------------------------------- 

Thanks, TAMATAM ; Business Intelligence & Analytics Professional 

--------------------------------------------------------------------------------------------------------