May 9, 2011 at 9:27 am
We recently moved from SQL Server 2005 (32 bit) to 2008 (64 bit). We have three MS Access 2007 databases from 32 bit operating systems (XP) that contain tables to be uploaded to SQL Server using SSIS packages. The SSIS packages executed from client (Visual Studio) run as planned. Installing the packages to SQL Server results in one package running as planned and the other two giving the below message.
Message
Microsoft (R) SQL Server Execute Package Utility
Version 10.50.1600.1 for 64-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.
Started: 10:06:52 AM
Error: 2011-05-09 10:06:52.24
Code: 0xC0209303
Source: HospitalLocation_Service Connection manager "\\vhapthshare\datawarehouse\Payne\Location_Service.accdb"
Description: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR. The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered -- perhaps no 64-bit provider is available. Error code: 0x00000000.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".
End Error
Error: 2011-05-09 10:06:52.24
Code: 0xC020801C
Source: Transfer data from access data base to Vista tbl_Hospital_Location_Service [1]
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "\\vhapthshare\datawarehouse\Payne\Location_Service.accdb" failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
End Error
Error: 2011-05-09 10:06:52.24
Code: 0xC0047017
Source: Transfer data from access data base to Vista SSIS.Pipeline
Description: component "tbl_Hospital_Location_Service" (1) failed validation and returned error code 0xC020801C.
End Error
Error: 2011-05-09 10:06:52.24
Code: 0xC004700C
Source: Transfer data from access data base to Vista SSIS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2011-05-09 10:06:52.24
Code: 0xC0024107
Source: Transfer data from access data base to Vista
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 10:06:52 AM
Finished: 10:06:52 AM
Any suggestions would be greatly appreciated.
Thanks in advance.
May 9, 2011 at 10:09 am
Hi, have you moved it to a new box or just upgraded the existing one? I appears that you are missing the access jet database engine dll on that server. I think this is an ODBC .dll if I remember correctly.
Let me know how you get on
Cheers
Dave
May 9, 2011 at 10:14 am
The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered.
Yes as Dave stated it appears that you are missing the proper DLL for Access.
Try creating an ODBC COnnection for Access and check for the DLL exist and the version.
I'm not sure if the following article applies to your situation:
Regards,
Welsh
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 9, 2011 at 12:40 pm
We moved to a new (virtual) box. We have downloaded the ODBC Driver for 32 bit Access (Microsoft.ACE.OLEDB.12.0) per the Microsoft Website and installed it. This was the action that the one process that works began working after.
The curious part to me is that the process works for one Acces Database but not the other two and I can not detect any differences other than location.
May 9, 2011 at 12:45 pm
That is interesting.
Can you create an ODBC Connection to the Other Databases and click on Test Connection?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 9, 2011 at 1:39 pm
"The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered -- perhaps no 64-bit provider is available"
This indicates it's expecting a 64 bit driver?
May 9, 2011 at 1:50 pm
If you create an ODBC Connection on the box, you will see a version number of the DLL.
That will tell you what you have.
It almost sounds like you have a 32 bit driver installed if one of the databases works.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 10, 2011 at 10:31 am
My thanks to all for your contributions. I found the solution and it was to (1) download the 32 bit driver and then (2) indicate "Use 32 bit runtime" in the Execution Options tab of the Job Step properties.
Thanks again.
May 10, 2011 at 10:41 am
I'm glad you found a solution to your problem.
Why did one of the Access Databases transformations work and the other two failed?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 10, 2011 at 11:43 am
I am not really sure why one worked and the others did not. I suspect my playing with it was that I set the parameter prior to realizing I needed the driver. Then when I loaded the driver it worked. when I went to ensure I had the parameter set, it was.
I suspect it was my haste in finding a solution and trying "anything" I could think of.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply