SSIS - Error Messages when looping through Excel Files

Hello all,

What's the trick to use a Foreach File in Folder loop when looping through
Excel files? I've gotten the loop to work with flat files before, but
doesn't seem to like Excel files.

I have 36 XL spreadsheets (one for each month over a time series) that need
to be imported into one table. Each XL file is set up the same: 5 sheets
(*** names are identical from file to file), each *** has a named range
on it (named ranges are identical from file to file). I need to import each
of these named ranges from each *** into the table.

On my data flow tab, I have 5 sets (one for each named range in the XL
files) of data flow components. Each set consists of an XL Data Flow
Source, a derived column transformation, and an OLE DB Destination.

Now if I have my XL Connection manager set to a specific file, everything
works fine. But if I set the connection manager's ConnectionString property
the variable the for each file loop is using, each of the XL sources in the
data flow has a red X and the following error message: Error 1 Validation
error. Data Flow Task: PssBk [309]: The AcquireConnection method call to the
connection manager "Excel Connection Manager" failed with error code
0xC0202009. ImportData.dtsx 0 0

I tried entering a default value for the variable (entered in the Value
column of the variable list).

First I tried the file name & path (\\server\share\path\XLFilename.xls).
When I did that, I got the following error message: Nonfatal errors
occurred while saving the package: Error at ImportData [Connection manager
"Excel Connection Manager"]: The connection string format is not valid. It
must consist of one or more components of the form X=Y, separated by
semicolons. This error occurs when a connection string with zero components
is set on database connection manager. Error at ImportData: The result of
the expression "@[User::varFileName]" on property "ConnectionString" cannot
be written to the property. The expression was evaluated, but cannot be set
on the property.

Next, I tried the same thing (file & path), but surrounded it in quotes.
Same error message

Then, I notice the ConnectionString property of the XL Connection Manager,
so I set the Connection manager to a specific file, copied the
ConnectionString, recreated an expression setting the ConnectionString to
the variable the Foreach loop is using, then pasted the Connection String I
copied into the value of the variable. All that did was set the connection
manager to one file. The Foreach loop was looping through the files, but
the one file was getting imported every iteration. The variable did not
appear to be updated making the connection string constant.

What am I missing here? How do I set up a package with a Foreach File loop
to loop through XL files?

Thanks for any help anyone can provide,

Conan Kelly


.