Looping through several Excel data sources in SSIS

I am attempting to use the foreach loop structure in an SSIS package to
loop through however many Excel files are placed in a directory and
then perform an import operation into a SQL table on each of these
files sequentially. The closest model for this that I was able to find
in the MS tutorial used a flat file source rather than Excel. That
involved adding a new expression to the Connection Manager that set the
connection string to the current filename, as provided by the foreach
component. That works just fine, but when I attempt to apply the same
method to an Excel source, rather than a flat file source, I cannot get
it to work. I see the following error associated with the Excel source
on the Data Flow page: "Validation error. Data Flow Task: Excel Source
[1]: The AcquireConnection method call to the connection manager "Excel
Connection Manager 1" failed with error code 0xC020200." I think that
it's just a matter of getting the right expression, and I thought that
perhaps I should be constructing an expression for ExcelFilePath rather
than the Connection String, but I have fiddled with it for hours and
haven't come up with something that will be accepted. Has anybody out
there been able to do this, or can perhaps refer me to some
documentation that contains an example of what I am trying to do?
Thanks for any help you can give.David,

The April 2006 update of SQL Server 2005 Books Online contains a
new topic titled "How to: Loop through Excel Files and Tables", at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/a5393c1a-cc37-491a-a260-7aad84dbff68.htm
or online at
http://msdn2.microsoft.com/en-us/library/ms345182.aspx

Why don't you see if that helps, and if not, let us know exactly
what point in that article something goes wrong.

The download page for BOL, for local installation, is
http://www.microsoft.com/downloads/...&DisplayLang=en

Steve Kass
Drew University

davidz wrote:
> I am attempting to use the foreach loop structure in an SSIS package to
> loop through however many Excel files are placed in a directory and
> then perform an import operation into a SQL table on each of these
> files sequentially. The closest model for this that I was able to find
> in the MS tutorial used a flat file source rather than Excel. That
> involved adding a new expression to the Connection Manager that set the
> connection string to the current filename, as provided by the foreach
> component. That works just fine, but when I attempt to apply the same
> method to an Excel source, rather than a flat file source, I cannot get
> it to work. I see the following error associated with the Excel source
> on the Data Flow page: "Validation error. Data Flow Task: Excel Source
> [1]: The AcquireConnection method call to the connection manager "Excel
> Connection Manager 1" failed with error code 0xC020200." I think that
> it's just a matter of getting the right expression, and I thought that
> perhaps I should be constructing an expression for ExcelFilePath rather
> than the Connection String, but I have fiddled with it for hours and
> haven't come up with something that will be accepted. Has anybody out
> there been able to do this, or can perhaps refer me to some
> documentation that contains an example of what I am trying to do?
> Thanks for any help you can give.