Let me start by saying that Excel makes for a horrible source. I have used SSIS packages to loop through files many times before, but never Excel (until recently). I know what you?re thinking. ?Gee, what makes Excel files so different?? Well, you can?t just use a single variable to update the connection string for the Excel connection manager. That would be way too easy. Here are the steps I had to take to get this to work.
First, of course, configure the data flow to pull from an Excel source. Then, put that into a ForEach Loop. I created a variable that points to an Excel file in the directory I wanted to loop through. Here is where it gets interesting. Select the connection manager and view the properties. Copy the ConnectionString value.
Now, in the expression field, click the ellipsis to add an expression. For the property, select ConnectionString and click the ellipsis.
In the expression editor, paste the ConnectionString value that you copied from the properties window.
Now, we have to make some odd changes. Right after ?Source=?, concatenate with the variable that is being used by the ForEach Loop. This breaks the connection string into two sections. Be sure to enclose both sections in double quotes. Also, take note of the added slash after the second equal sign and another one right after HDR=NO. The expression should look like this?
Yeah, that is not at all an inconvenience. Click OK to close out the expression editor and you?re good to go.