I got a comment yesterday (well, it was yesterday as I typed this post, but I’m not actually publishing it until the next day… damned air travel…) on my “Loading Multiple Excel Files with SSIS” post asking if it was possible to use the same technique to loop over multiple Excel workbook files with different worksheet names and still have a single package to do the work. The short answer is yes, but it depends on these workbooks that have different worksheet names to have identical worksheet structures, and that’s a pretty big if.
I’m personally skeptical that you’re going to run into this situation in the real world. From my experience, unless files come from a single source, there are almost always meaningful differences (although they may be small) between them. And if these hypothetical Excel workbook files come from a single source, they’re probably going to have the same name, right?
In any event, let’s work under the assumption that we have Excel workbook files with the same data but with a different worksheet name. The steps below will demonstrate how to update the sample package from my earlier post to work in this scenario. I’m not going to write all of the VB.NET code that will be required (partly because I’m writing this in the airport while waiting for my flight and don’t have the necessary components installed to make it work, and partly because this sounds like an ideal “exercise for the reader”) but I’ll cover everything else. Here’s what you need to do:
First, we need a new XLS file to serve as input. Copy one of the existing XLS files (I’m using the last post as the starting point, so if you didn’t follow those steps then, you’ll need to do it now in order to follow along) and then open the copy in Excel. Rename the first worksheet from Sheet1 to DifferentName. Save the file and close it.
Open the SSIS project created in the previous post in Visual Studio, and open the SSIS_Excel_Loop.dtsx package.
Add a new variable named WorksheetName of type string with the value “Sheet1$”
Edit the Excel data source in the data flow to use the “Table name or view name variable” Data access mode and select the WorksheetName variable from the drop-down list.
Click on the Preview button to verify that the data source still works.
Add a Script task to the control flow inside the Foreach Loop container and connect it to the Data Flow task with a success precedence constraint.
Right-click on the newly-added Script task and select Edit from the pop-up menu. On the Script tab within the Scrip Task Editor window, add WorksheetName to the ReadWriteVariables list and FileName to the ReadOnlyVariables list.
Click on the Design Script button to open the Visual Studio for Applications (VSA) development environment.
Dim sheetName As String
Dim fileName As String = CStr(Dts.Variables(“FileName”).Value)
If fileName.Contains(“email_book_03.xls”) Then
sheetName = “DifferentName$”
sheetName = “Sheet1$”
Dts.Variables(“WorksheetName”).Value = sheetName
Dts.TaskResult = Dts.Results.Success
Postscript: I also see that while I was in the air yesterday I got a few additional comments posted from “Romain” on looping over the tabs in a workbook to load all of them instead of just the first tab. I’m afraid I cannot read the French solution to which he linked, but the same technique not listed in the code above is what you’re going to need to do that. You’ll need to loop through each Worksheet object in the Workbook object for the current XLS file, using the Excel object model to do so. Probably the easiest way to do this is to have a For Loop Container in your control flow contained within the existing Foreach Loop Container that loops through the multiple Excel files. Within the For Loop have a script task that fetches the name of the next sheet following the last-fetched “current” sheet (or the first sheet in the book on the first pass through the loop) and also sets a Boolean flag indicating that the sheet name being fetched is the name of the last sheet in the workbook. Configure the For Loop to exit when this flag variable is set to true.