Loading Multiple Excel Files with SSIS - Part Two

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.

Edit the code to look like this:

Public Sub Main()

Dim sheetName As String
Dim fileName As String = CStr(Dts.Variables("FileName").Value)

If fileName.Contains("email_book_03.xls") Then
sheetName = "DifferentName$"
Else
sheetName = "Sheet1$"
End If

Dts.Variables("WorksheetName").Value = sheetName

Dts.TaskResult = Dts.Results.Success

End Sub

This is where you’ll need to do a little work on your own. Although this scenario will execute and work as desired for the sample files, it’s obviously not going to be sufficient in the real world. Instead, you’ll need to use the Excel object model, perhaps through a custom .NET assembly that wraps the COM Office DLLs, perhaps through Visual Studio Tools for Office (I’m not going to take the time to build the former and can’t download and install the latter without an internet connection) and use that API to retrieve the worksheet name of interest. This will probably be the first worksheet in the workbook, but your requirements may vary.

The key portions of the VB.NET code you’ll need to write for the Script task are that you read from the FileName package variable to know what XLS file you’re working with, and then write to the WorksheetName package variable to tell the rest of the package (specifically the Excel data source component in the data flow) the name of the sheet to use. Pretty simple, right?

Well, it works on paper, but as I mentioned early on I suspect that you’ll find that there are differences beyond the sheet name that you’ll need to handle. Because of this you’ll need to ensure that your package has robust error handling so that when these differences appear at runtime the package can handle them gracefully.

Good luck!

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.

Again, good luck! I'm not going to have time to write the Excel sheet-looping code myself, but when you get it working I'd love to see it. ;-)