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:\Clients\Dep Comm\BEA\BEA_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:

1. Created excel connection called, “Dynamic Excel Connection Manager,” that initially pointed to one of the excel workbooks.
2. Went to the connection properties by right clicking the connection manager.
3. Expanded Expressions and clicked the ellipsis button to bring up property expressions
4. Chose Connection String in the Property.
5. Clicked the Expression Ellipsis button.
6. 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] + "\""

7. Clicked the Evaluate Expression button to get the following:
   1. Provider=Microsoft.Jet.OLEDB.4.0;Data Source=;Extended Properties=""
8. Clicked Ok button
9. Inserted a Data flow task inside the foreach loop container.

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

1. Set the Foreach loop container Delay Validation to true.
2. Set the Data Flow Task Container Delay Validation to true.
3. Set the Dynamic Excel Connection Manager Delay Validation to true.
4. Set the SQL Server Connection Manager Delay Validation to true.
5. Set the Package Delay Validation to true.
6. 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: 0x80004005Description: "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: 0x80004005Description: "Unrecognized database format 'E:\Clients\Dep Comm\BEA\BEA_Test_Source\PersonnelExpense_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

Are there headers on the Excel spreadsheets?

The error is looking for a database format where the headers are matched as column headers.

If there are no headers, it doesnt' know how to match it.

Just my twist on it,

Adamus

|||

Hi Adamus,

I appreciate your feedback. To answer your question, the first row does contain column headers.The data flow task has an excel source file which uses the Dynamic Excel Connection manager that I mentioned in my original post. The excel source file connects to a derived column data task that creates derived columns and changes the data type to reflect that of the destination. The derived columns is then connected to the SQL Server destinationation table that matches the excel header columns to the destination table columns.

What should I do with the connection string that I mentioned in my first post? I have provided it here for you or anyone else to review.

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +@.[User::ExcelFile] + ";Extended Properties=\"" + @.[User::ExtProperties] + "\""

Can you or anyone else tell me what I am doing wrong here?

Thanks!!

Lee

|||

Can you actually import at least one single file? forget about the foreach loop and the expression in the connection manager.

I just did a search on 'Could not find installable ISAM ' and found this:

http://support.microsoft.com/kb/209805

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=477902&SiteID=1

So, before going deeper, try to connect to a single execel file.

BTW, I just found a test package I created long time ago, and I see some differences with your approach:

I have only one variable in my for each loop container to hold the file name; and since I configured the loop container to retrive 'Fully Qualified" file name; the variable contains the path and the file name. In the excel connection manager I applied an expression to the ExcelFilePath property (not to the Connection String). The expression is just: @.{User::VarFileName] ; which is the variable I use in the loop container. I don't mess with the extended properties.sql