April 14, 2009 at 10:43 am
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
April 14, 2009 at 11:32 am
Do you have any configurations for your connection (to excel)
April 14, 2009 at 11:35 am
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.
April 28, 2009 at 8:56 am
I am currently having the same problem did you ever have a resolution to this issue.
And If so can I please have it
April 28, 2009 at 8:05 pm
Reading between the lines "Close the open file and rerun" would appear to be the resolution in this case.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 29, 2009 at 5:36 am
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
April 29, 2009 at 5:46 am
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?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 29, 2009 at 6:09 am
Here you go. I places an attachment of a screen shot
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply