June 20, 2009 at 12:31 pm
I have a SSIS package that uses a Foreach container to dynamically name excel workbooks and deposit data within the worksheets.
I ran this package once in development in BIDS and it worked fine. If I want to run it it again I get the following error:
Error at Package [Connection manager "Excel Connection Manager"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data.".
Error at Data Flow Task [Excel Destination [52]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Does anyone have any information on how I can solve this?
June 20, 2009 at 8:30 pm
Is there any difference between the package that worked and the package that failed? If no, presumably logging out, back in, and re-running works again, but only once?
Can you explain in a bit more detail what your Foreach loop is doing?
--Edit
Also, if your Excel spreadsheets have dynamic names, what is your Excel connection manager pointing to?
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
June 20, 2009 at 11:17 pm
There is no difference between the package that worked and the on that failed. I ran it the first time and it worked. I then saved it and ran it again then it gave the error. A red cross appeared on the excel destination icon in the data flow within the foreach loop container.
I have created an excel template. I have an execute sql task outside the Foreach container which creates an object variable, User::CurrentCode, to hold the data which will be used to name the excel file dynamically.
Within the Foreach container is a copy file task and data flow. The copy file task copies my template. it is parameterized by a variable,User::TemplateFile, that uses the expression :
'ContrctTerms_" + @[User::CurrentCode] + ".xls' to name the Excel workbook.
The excel connection is pointing to my template. I set the expression for ExcelFilePath property to: @[User::TemplateFile].
The data flow task then sends data to the excel destination based on a paramatized sql statement.
June 20, 2009 at 11:34 pm
Apologies if this is an obvious question, but is there a chance that you had the file open in Excel when you ran the package?
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
June 20, 2009 at 11:40 pm
the file is definately closed.
June 20, 2009 at 11:57 pm
If you hover your mouse over the red cross on the Excel destination, it should give you a tool-tip popup describing what the problem is - what does that say?
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
June 21, 2009 at 12:10 am
The tool tip says:
SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
June 21, 2009 at 3:23 am
OK, I'm running out of ideas, I'm afraid. If you edit the connection and save it again (after verifying the destination), does the error go away?
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
June 21, 2009 at 3:50 am
I edit the connection manager and save it the erroris still there. For some reason once it has run. It will not allow ssis to acces the files. I am wondering if there is something thatI need to do with the Excel template?
In the the excel connection manager do i need to set the reatinsame connection to true?
June 21, 2009 at 11:56 pm
I have realised why it wasn't working. I did not put the correct directory in the User::TemplateFile expression. Initially it was set to :
'ContractTerms_" + @[User::CurrentCode] + ".xls' .When I changed it to
''s:\\Informatics\\Information Managemnt\\Contract Terms\\ContractTerms_" + @[User::CurrentCode] + ".xls'' it worked.
My Bad!!
June 22, 2009 at 12:10 am
Great! For added transportability, I would suggest that you consider using a UNC path rather than a network drive path ...
Phil
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
May 2, 2012 at 9:15 am
I got the same problem. I have correct path and file name in the variable declaration. But when I execute packge, it gives the same error. OLEDB ERROR.
Can you please guide me on this. Its making me crazy.
Thanks.
August 17, 2016 at 4:24 pm
Did you find any solution, as i am running into same error.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply