May 5, 2010 at 8:26 am
Platform: Windows Server 2003 R2 64bit Service Pack 2
Soft: Sql Server 2005 64bit Standard Edition
Scenario:
1.Creating an SSIS package to copy data from sql server to an Access DB.
2.The package uses a Microsoft Access DB as a destination (usng JET4.0 connection manager / data source)
3.The package can execute without fault, when executed WITHIN the SSIS/BIDS development software.
4.The package can be deployed to a sql server 2005 destination
5.When the package is manually executed through sql server, the folowing error is thrown:
"Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154."
and
"Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "t01102009-31122009" failed with error code 0xC0202009"
It seems that the package cannot hold the connection to the Access DB, is this a 64bit issue as higlighted by other posts?
This only occurs when attempting to connect to Access/Excel files.
May 5, 2010 at 8:32 am
This may be a silly question but did you verify that the server and the user executing the job has access to the path of the Access DB. I would not say this is a 64 Bit problem. based on the errors though I would think there is some reason that the connection can not be made to the mdb. this usually happens when deployed becuase the security permissions change ie you have permission to the file but the server does not. Or the server does not have a direct path to the file either do to drive mapping or file location.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
May 5, 2010 at 8:33 am
Please explain what you mean by "manually executed through SQL Server"
I guess you mean as part of a job. Are you logged in to SQL Server as the same user who was able to run the package in BIDS?
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 5, 2010 at 8:40 am
Dan,
Yes, the Access file resides locally and the server has correct permissions.
May 5, 2010 at 8:44 am
I am interested in the answers to the other 2 posters questions as well.
But I can almost guarantee that even if it is a permissions issue it will ALSO be a 32/64 bit problem. There are no 64-bit Jet drivers and access to Access uses Jet. You will need to call the package using the 32-bit version of DTExec, not as easy in 2005 but very doable.
CEWII
May 5, 2010 at 8:46 am
Phil,
Sorry to clarify, by manually i mean, creating a job under sql server agent and executing through right click, start job at step etc..
I am logged into sql server as the same user who was able to run the package in BIDS, the package is also password protected, the password is passed into the job on creation.
May 6, 2010 at 3:22 am
Answer from Elliot W (use of 32bit DTExec) was spot on.
One thing to note. If you password protect your ssis packages, cmdexec does not allow the use of /FILE and /PASS arguments concurrently.
May 6, 2010 at 8:50 am
That is good to know..
CEWII
May 7, 2010 at 12:13 pm
Site/page doesn't appear to be there anymore but there was a notice out in late Dec 2009 that suggested the Office 2010 (beta) had 64 bit jet drivers. So definitely not for production environments and assuming MS is still going to deliver them there might be a solution in the future. This was the location of the beta drivers but it appears to have been removed http://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en
Regards, Andrew
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply