Here is a bit of SSIS design that some users may find helpfull. Suppose you have an Excel file with multiple tabs, all with the same data. Or, you get a new monthly file each month, but the file name and/or the Tab name is different each time.
Using a ForEach loop, you can loop over all the (Excel) files in your folder, and using the write up in my post about looping over flat files, you can get to each one programatically inside SSIS. This will take care of the situation where the file name changes each month; your loop will execute only once.
But what if you a) have multiple tabs in your Excel file, or b) don’t know in advance what the name(s) of the tab(s) will be. You need some way to examine the Excel file in advance of the Data Flow and get the tab name(s).
I’m going to simplify this scenario and assume we are working with only one file, with multiple tabs. If you have multiple files to work with, simply encase everything here in a For Each loop configured to handle the multiple files.
Start by creating a Variable named TabList, of type Object. This will hold the list of tab names. Add a Data Flow, and in it drag in a Script Task. When prompted, set it as a Source. On the Inputs and Outputs tab, click on the Output 0 and add a Column called TabName, of type string[ST_STR]. In the script itself enter the following code under the CreateNewOutputRows sub:
Dim cn As OleDbConnection = New OleDbConnection(excelFile)
Dim tablesInFile As DataTable = cn.GetSchema(“TABLES”)
Dim tableCount As Integer = tablesInFile.Rows.Count
Dim currentTable As String
Dim columnRestrictions(3) As String
For Each tableInFile As DataRow In tablesInFile.Rows
currentTable = tableInFile(“TABLE_NAME”)
If Len(currentTable) = InStr(currentTable, “$”) Or _
Len(currentTable) = InStr(currentTable, “$”) + 1 Then
Output0Buffer.TabName = currentTable
I’ll leave it to you to edit the first line of code to get the proper Connection String for your particular case.