Receiving Errors When Using Foreach Loop And Excel Connection Manager...

Purpose: Need to import excel source data into SQL Server 2005 tables. Excel source data comes in nulitple excel files with the same structure but different data. I would appreciate someone taking a look at the following information and notifying me of what I am doing incorrectly.

I Inserted a foreach loop container, a data flow task located inside the foreach loop contaiiner, an excel and SQL Server 2005 connections.

After trying multiple times I went the following URL and followed step by step direction on how to connect excel workbooks dynamically: http://msdn2.microsoft.com/en-us/library/ms345182.aspx . I also used http://www.sqlstrings.com/ as a reference when creating the connection string.


Creating a Foreach Loop Container:

1. Opened foreach loop container 2.Set the Enumerator to 'Foreach File Enumerator" and configured the enumerator by setting the directory location and file base name to E:ClientsDep CommBEABEA_Test_Source and *PersonnelExpense*.xls respectively. 3. Clicked Variable Mapping; created two variables called, "ExcelFile", and "ExtProperties" and closed out of the foreach loop container.

I. Created Excel Connection:

Created excel connection called, €œDynamic Excel Connection Manager,€? that initially pointed to one of the excel workbooks.
Went to the connection properties by right clicking the connection manager.
Expanded Expressions and clicked the ellipsis button to bring up property expressions
Chose Connection String in the Property.
Clicked the Expression Ellipsis button.
Put the following inside the Expression multi line text box:
A. "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::ExcelFile] + ";Extended Properties="" + @[User::ExtProperties] + """

Clicked the Evaluate Expression button to get the following:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=;Extended Properties=""
Clicked Ok button
Inserted a Data flow task inside the foreach loop container.

II. Configured Tasks that is associated with Dynamic Excel Connection Manager or Package:

Set the Foreach loop container Delay Validation to true.
Set the Data Flow Task Container Delay Validation to true.
Set the Dynamic Excel Connection Manager Delay Validation to true.
Set the SQL Server Connection Manager Delay Validation to true.
Set the Package Delay Validation to true.
Package Locale ID set to English


Ran the package after connecting the excel source data flow to the OLEDB destination and have inserted part of the error in this post. Please see below.

Error: 0xC0202009 at Package, Connection manager "Dynamic Excel Connection Manager": An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Could not find installable ISAM.".

I modified the connection string after receiving the error by removing the extended properties. The following is the modified connection string: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::ExcelFile]

I repeated step I.6 above and received the following expression: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=

I ran the package and received the following error in part: OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unrecognized database format 'E:ClientsDep CommBEABEA_Test_SourcePersonnelExpense_OCCs_051007.xls'."


I did not find anything helpful when I searched for the above errors and would very much appreciate anyone€™s assistance on this issue as this issue needs to be taken care of ASAP.

Does anyone have any ideas as to why I received this error and what can I do to resolve this issue?

Your assistance in this matter is truly appreicated!
Thanks!!
Lee