Problem with foreach loop through excel files

Question

Post reply

Problem with foreach loop through excel files

cynthia.parker

Ten Centuries

Points: 1376
More actions
April 14, 2009 at 10:43 am

#208324

Hi, all. I'm using SSIS to import excel files. I have a foreach file loop that works correctly but errors out at the end and I can't figure out why. When I check the table all of the data from all of the excel files has been correctly imported, but every time I get the error message:
[Excel Source [84]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "FCT1" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
and then
[SSIS.Pipeline] Error: component "Excel Source" (84) failed validation and returned error code 0xC020801C.
Searches for these two don't turn up anything specifically useful.
The one thing I've done in the ForEach File loop container that is a little different is the use of two variables to specify both the filename and the excel worksheet name. The worksheet name is essentially the same as the filename. For instance, the filename may be "0109FCT1.xlsx" and the worksheet I need to import from is "0109FCT1$". So I've set up the Collection of the ForEach collection to be "Name Only", which is assigned to the variable var_FCT1FileName, then I have another variable var_FCT1SheetName which gets evaluated as the expression @[User::varFCT1FileName] + "$".
Anyway, as I said, the whole thing works. When I look at the table, all of the rows are there from the three spreadsheets I have right now. But every time it errors at the end. The problem is that I want to use this package as part of a larger ETL package, and it messes everything up by erroring out.
Any suggestions?
- Cindy

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

vishal.gamji SSCertifiable Points: 6126 More actions · Answer 1

Do you have any configurations for your connection (to excel)

cynthia.parker Ten Centuries Points: 1376 More actions · Answer 2

I just found out I had one of the excel files open. It's funny that it worked, but the error actually came from the open file.

mfink SSCommitted Points: 1632 More actions · Answer 3

I am currently having the same problem did you ever have a resolution to this issue.

And If so can I please have it

Phil Parkin SSC Guru Points: 246962 More actions · Answer 4

Reading between the lines "Close the open file and rerun" would appear to be the resolution in this case.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

mfink SSCommitted Points: 1632 More actions · Answer 5

Phil

this seems to be a problem. After I save the dtsx file, close and try to reopen the file is in an error state and I cannot reopen the dtsx file.

Mark

I have to re enter the package

Phil Parkin SSC Guru Points: 246962 More actions · Answer 6

So the package itself cannot be re-opened? That's quite different from the problem with an Excel source being open at run time.

Can you paste the error message that you are receiving when you try to open the file?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

mfink SSCommitted Points: 1632 More actions · Answer 7

Here you go. I places an attachment of a screen shot