Problem

Recently came across a problem when writing an SSIS package. Had done the dynamic names of SSIS Excel files with the same metadata but using dynamic sheet names for the metadata came as a bit of a challenge.

Solution

After a lot of Googling, simplified the steps to the following:
  1. Declare two variables of String type:
    • SheetName: To fetch names of the sheet from the Excel file
    • FileName: To fetch names of Excel files.
  2. Create a script task from which you want to fetch your sheet names
  3. 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
  4. You can loop the sheets if there are multiple sheets.
  5. Then, create another variable with the name of SQL command and give the following in its expression:

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

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

  6. Give this variable in your Excel source
  7. 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
  8. Give this variable (usually configurable ) a path to the folder where your Excel files reside.
  9. Then use a foreach loop task with the following settings:
  10. Then, in this foreach loop task, place your script task and the data flow task where the Excel is getting transformed.

See Also