Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
wyliec2
Starting Member
1 Post |
 Posted - 2010-07-06 : 18:43:26
|
Help (please!) - I'm migrating some Access databases from 2003 (.mdb) to 2007 (.accdb). We have a SQL Server 2005 64-bit machine that imports tables from these databases (has been working fine on .mdb connections for several years).Having been through the 64-bit problems when we originally installed SQL 2005, I'm familiar with the driver/provider 32-bit issues.Anyway - I can manually run the SSIS package in BIDS on my workstation and it works fine (so seems like 64-bit issue). However, when I run the package manually in BIDS on the server it fails (and BIDS runs in 32-bit mode even on 64-bit machine). Now the package fails on the server either in BIDS or as SQL Agent job. Both scenarios produce this error:[OLE DB Source [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "\\svhplan2\access db\access_test\IMPORT_Work_Area_ACCDB.accdb" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.[DTS.Pipeline] Error: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.This is using the Microsoft Office 12.0 Access Database Engine OLD DB Provider.This is same server, accounts, folders, etc. that work fine on 2003 .mdb files using the Microsoft Jet 4.0 OLE DB Provider - so it's not a path/privileges problem.Even more puzzling is that in BIDS on the server, the 'Test Connection' succeeds in Connection Manager. And in the Data Flow task the column mappings are all there and I can see the data in 'Preview' in the OLE DB Source - so it is reading the .accdb database and table. It just fails when I execute the Data Flow Task.Any suggestions would be greatly appreciated!!!  |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
|
|
|
|
|
SSIS Pkg Fails On Access 2007 Connection