Using dynamic excel file names with dynamic sheet names

Recently, I came across a problem when writing an SSIS package. I had done the dynamic names of SSIS excel files with the same meta data but using dynamic sheet names for the meta data came as a bit of a challenge to me. After a lot of googling, I simplified the steps to the following



-Declare two variables of String type

• SheetName - To fetch names of sheet from the excel file

• FileName - To fetch names of excel files.

- Create a Script task from which you want to fetch your sheet names 





-Write the following code in the script task

Public Sub Main()


Dim xlApp As Excel.Application = New Excel.ApplicationClass()

Dim xlWorkBook As Excel.Workbook

Dim xlWorkSheet As Excel.Worksheet



xlWorkBook = xlApp.Workbooks.Add(Dts.Variables("FileName").Value)

xlWorkSheet = CType(xlWorkBook.Sheets(1), Excel.Worksheet)

Dts.Variables("SheetName").Value = xlWorkSheet.Name


Dts.TaskResult = ScriptResults.Success
End Sub


-You can loop the Sheets if there are multiple sheets.


-Then, create another variable with the name of SQLcommand and give the following in it's expression:

"Select * from ["+ @[User::SheetName]+"$A1:CJ]"

Note: I used A1:CJ as my own range, so give the range you want the sheet to pick up.



-Give this variable in your excel source



-Now, to come to the point where all the excel files in the folder are traversed dynamically. To do this, create another variable called "SourceFile" of String data type
-Give this variable (usually configurable ) a path of the folder where your excel files reside.
-Then use a Foreachloop task with the following settings: 


Then, in this Foreachloop task, place your script task and the data flow task where the excel is getting transformed.