Looping over Excel Files Using For Each Loop Container

i am trying to loop over excel files but i am getting an error like Aquireconnection method call to the connection manager failed with the error 0x08000408 and also get oledb source error,Oledb record is available Microsoft jet engine can not found,could not found installable ISAM

  i am using Excel 2005(xls iles) and windows Xp Service Pack 2

Please help out in this problem

Thanks in advance

Saraswathi

A couple of questions for you:

1. Are you running this package on a 64-bit machine?  If so, you have to run the package in 32-bit mode.  Excel does not support 64-bit inside SSIS.

2. Are you using the Excel Source and Excel Connection Manager in your package?  If not, try using those.

3. When you set up the ForEach Loop, did you put the expression on the Excel Connection for the Connection String or for the file location?  On the excel files you have to put that expression to populate the file location on the file location not on the connection string, unlike with the flat file, the Excel connection string contains a bunch of other information that is needed in addition to the file location.

4. Is the package even running or is it failing at validation?  If it is failing at validation you will need to set the data flow to delay validation = True so that it doesnt try to validate the connection ahead of running.

Thanks for ur reply BradSchacht

yes  i  am running  my package in 32 bit mode, i have used excel source  and excel connection manager,i also put the expression on excel connection for the connection string ,i have done whatever u asked but still i am getting the same error,i don't know what is the problem

Please help me in this problem very urgent

Thanks in advance

Saraswathi

Mike Davis had a blog post on looping through Excel files a while back that might be helpful to you:

http://www.bidn.com/blogs/MikeDavis/ssis/625/loop-through-excel-file-in-ssis

Hope it helps,

Keith

Still I am getting the error like Could not find installable ISAM, and Acquire connection method call to the excel connection manager failed

Thanks in advance

Saraswathi

Hai guys These are the list of errors i am getting while looping over excel files using ssis

[Connection manager "Excel Connection Manager"] Error: 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.".

[DTS.Pipeline] Error: component "Excel Source" (586) failed validation and returned error code 0xC020801C.


[Excel Source [586]] Error:The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.

Warning: The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

It is very urgent please help me in this problem

Thanks in advance

Saraswathi

Could you please copy the entire connection string that is in the excel connection manager and put it in a post?  One issue that I have found comes up with excel is with the extended properties.  I would like to see mainly what version of excel it has listed in the extended properties.  If is not that the other reasons I have found in researching the ISAM issue is dlls not being registered or driver corruption on the machine.

Thanks for ur Response

the following is the expresion  i have used in Connection String Property in Excel Connection Manager Properties

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ @[User::ExcelFilePath]  +" Extended Properties=\"Excel 8.0;HDR=YES\";"

Thanks in advence

Saraswathi

Is there a reason you put the expression on the connection string instead of just on the Excel File Path?  I would try doing that next.  Remove the expression from the connection string and just apply an expression setting the property "Excel File Path" with that variable.  This will ensure that the connection string is definitely formatted correctly and remove any confusion.  You may need to remove the connection manager and recreate it to ensure that everything gets refreshed.  Make sure that the entire path is in that variable as well, including drive letter and folders.  ie C:\Documents\ExcelFiles\Excel.xls

BradSchacht said:

Is there a reason you put the expression on the connection string instead of just on the Excel File Path?  I would try doing that next.  Remove the expression from the connection string and just apply an expression setting the property "Excel File Path" with that variable.  This will ensure that the connection string is definitely formatted correctly and remove any confusion.  You may need to remove the connection manager and recreate it to ensure that everything gets refreshed.  Make sure that the entire path is in that variable as well, including drive letter and folders.  ie C:\Documents\ExcelFiles\Excel.xls

I agree with Brad, remove the variable from the connection string.  Then you can set the ExcelFilePath variable within the Expressions for the Excel File Connection.

- Rob

Thanks for ur responce

 as u guys said that remove that connection string property,but when i removed that connection string and i only kept excel file path variable i am getting the erroe that connection string property format is not valid,the format should be like x==y,i am getting above error

I have changed that variable

ExcelFilePath= Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Spin_May_Jun_Jul\Spin_Upload_July.xls;Extended Properties="Excel 8.0;HDR=YES";

and i kept this variable in excelconnection Manager Properties,Expression as This Variable i.e ExcelFilePath

now i am not getting the previous error,i am able to run the package but i am getting only the one excel file data three times,but i need to get the data from the three excel files,each file has single sheet that sheet name is Sheet1$(Same sheet name in three files

is there any propeties i need to change

Please help me

Thanks in advance

Saraswathi

You need to have the ForEach Loop container set a variable to the file name and path of the excel file. Then in the expressions of the Excel Connection Manager, set the ExcelFilePath to that variable.

As Rob said, you only want to set the expression on the ExcelFilePath.  There should be no expression on the variable that is populating that or on the ConnectionString.  You are likely going to have to delay validation on that data flow task inside the ForEach Loop that Rob describes above.

Thanks for all ur responces,suggestions and help

now my package is successfully running,i found the mistake that  i have not set the delay validation = true for the excel connection manager so that i got the error like method of acquireconnection method call to excel connection manager failed.

  now i am not getting  any errors

i am able to iterate on Excel files and excel sheets successfully

Thanks & Regards

Saraswathi