SSIS - Loading data From Different Excel File and form different sheets dynamically.

Very often i have seen people asking question like is their a way where i can load data from different sheets of an excel dynamically Or can i load data from different excel files dynamically.I will say yes u can definetly do that. This post tells about loading data from different Excel sheets Dynamically and also getting data from different sheets of excel file dynamically.

Following are the steps explained in detail .

Step1:
Create a Excel File Connection by pointing to the directory where the excel files will be stored.

Select a proper path by browsing it to any Excel File and click OK.

Step2 :

Declare A Variables at Package Level One for Path and Other For File Name.
For Example: PathName,FileName
Here in my example i have hard coded the value for PathName i.e.
PathName = 'D:\Paper_Daddy\Different Centers\'
And FileName Initially give a value of any excel sheet located in above path.

Step3: 
Use a For Each Loop Container to get the file names dynamically.

Connections and Properties of For Each Loop container is as shown below.

Variable Mapping For Each Loop container is as shown below.

So above Step3 ll fetch the the file names dynamically from the path mentioned in the variable.

Next set of steps is for getting the sheet names of each excel file dynamically.

Step4:

Use a For Each loop Container for getting sheet names dynamically.Declare one more variable to store work sheet names say "WorkSheetName" and provide any one of the  sheet name of File mentioned in above file Name variable (In step2).

This For Each loop container must be inside the container created in Step-1.Add a Data Flow task inside the second For Each Loop Container.

Below are the Connections for For Each loop container that extracts worksheet names of each excel sheets.

 
In above screen shot, the Connection string can be any of the excel file.Double click on the above connection string and Select all tab and change the settings as shown below

The variable "WorkSheetName" must be mapped with the settings as below.

The variable WorkSheetName which holds the value of wrksheetnames of different excel sheets cabn either be inserted into a table in database or can also be used with in the Excel source connection inside Dataflow task.

Thus this post helps in extracting the wrksheetnames of dynamic excel sheets .