SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR .
I have an SSIS package which I migrated from SQL Server 2005 to 2008 by making the neccessary changes and imported the package into Integration Services file system. When I run the package under Integration Services, it runs without any problem.
When I set it up and run it as a SQL Server agent job, I receive the following error message: Not sure how to resolve this;
Message
Executed as user: NASE\SVC.SQLAGT.PAT. Microsoft (R) SQL Server Execute Package Utility
Version 10.0.4000.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 3:27:42 PM
Error: 2011-05-26 15:27:44.33 Code: 0xC0209303
Source: FP3DataImportPBGF Connection manager "Source (PBGF)"
Description: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR.
The requested OLE DB provider Microsoft.Jet.OLEDB.4.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-26 15:27:44.33
Code: 0xC020801C Source: DTSTask_DTSDataPumpTask_1 OLE DB Source [1]
Description: SSIS Error Code
DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.
The AcquireConnection method call to the connection manager "Source (PBGF)" 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-26 15:27:44.33
Code: 0xC0047017 Source: DTSTask_DTSDataPumpTask_1 SSIS.Pipeline
Description: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.
End Error Error: 2011-05-26 15:27:44.33
Code: 0xC004700C Source: DTSTask_DTSDataPumpTask_1 SSIS.Pipeline
Description: One or more component failed validation.
End Error Error: 2011-05-26 15:27:44.33
Code: 0xC0024107 Source: DTSTask_DTSDataPumpTask_1
Description: There were errors during task validation.
End Error DTExec: The package execution returned DTSER_FAILURE (1).
Started: 3:27:42 PM
Finished: 3:27:44 PM Elapsed:
1.872 seconds. The package execution failed.
The step failed.
May 26th, 2011 4:42pm
It seems that you are now on a 64 bit machine. And that there is no 64 bit OLE DB provider Microsoft.Jet.OLEDB.4.0
so your fix is in running this SQL Agent job in 32 bit mode
see this
link for the setting
Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
May 26th, 2011 4:47pm
You should upgrade your package to use “Microsoft.ACE.OLEDB.12.0”
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d&displaylang=enJeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA Blog: MrWharty.wordpress.com
May 26th, 2011 9:25pm