How to read Multiple Excel Sheets in one Excel File in SSIS Part -3

Click on OK Button of Expression Builder windows and the Property window. Then Property window will be looks like in the below Screen shot. 

 

 

On the Control Flow, Drag and place two Foreach Loop containers one within the other. The first Foreach Loop container named Foreach Loop First will loop through the files.

The second Foreach Loop container named Foreach Loop Second will through the sheets within the container. Within the Foreach Loop Second, place a Data Flow Task that will read the Excel files and load data into SQL.

Configure the Foreach Loop First. Please find the screen shot below. Right click on the Foreach loop First, click on Edit. Then Foreach Loop Editor will be displayed.

 

Selection Collection tab then Click on Expression and then configure the properties. Please find the screen shot below.

 

 

 Click on Browse button select the folder. Please find the screen shot below.

 

 

Then Click on Variable Mapping and configure the property. Please find the screen shot below.

 

Then Click on OK Button

Select Second For each loop right click on the For each loop  second click on Edit. Then  For each Loop editor window will be displayed. 

Click on Collection Tab and Configure the properties . Please find the screen shot below.

And the click on Variable mapping and configure the properties. Please find the screen shot below.

Then Click on OK button of Foreach loop container.

Next Double click on Data Flow task, it will redirected to Data Flow Task Window. Drag one Excel Connection Source, Data Conversion Transformation and OLEDB Destination. Please find the screen shot below.

Configure the properties in the below section. Right click on the Excel connection Manager click on New  Then Browse the Excel File path then click OK Button of Excel Connection Manager. Please find the screen shot below.

Then Click on OK Button of Excel Connection Manager. Select the  Data Access Mode  to Table name or View name Variable, and Variable name to Sheet name. Please find the screen shot below.

Then Click on OK Button. Then configure the Derived Column properties.  Please find the screen shot below.  The Column State getting conversion error . So I have changed the data type  of state.

Then Next we need to configure the OLEDB Properties. Right click on OLEDB Destination and then click on Edit. The we need to select the Connection Manager , Data Access Mode to Table or View Fast Load and Table name. Please find the screen shot below.

Please click here for Part-4

How to read Multiple Excel Sheets in one Excel File in SSIS Part-4