Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". - MSAccess

Question

Hello,

 

I am running Windows Server 2003 R2 Enterprise Edition SP 2

 

I have created an integration package in VS 2005 which extracts data from Access 2000 and imports it into a table on SQL server 2005 running on this server.

 

The package runs ok in Visual Studio 2005, but when i schedule it as a step in 2005 SQL Agent to execute as a SQL Server Integration package using SQL Agent Service Account it comes with the following error -

I did a bit of research which suggests this is due to SQL 64 bit and Windows 64b bit, however I am running 32 bit !! also the sql server agent is a domain admin user...can you gurus please help ?

 

 

 

 Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started:  10:07:42 AM
Error: 2007-10-31 10:09:13.85
   Code: 0xC0202009
   Source: Copy all Data to Reporting Prod Connection manager "Access Allocations.mdb"
   Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040154.
An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  Description: "Class not registered".
End Error
Error: 2007-10-31 10:09:13.85
   Code: 0xC020801C
   Source: 5_Copy vw_Allocations 5_Access Allocations [1]
   Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Access Allocations.mdb" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.
End Error
Error: 2007-10-31 10:09:13.85
   Code: 0xC0047017
   Source: 5_Copy vw_Allocations DTS.Pipeline
   Description: component "5_Access Allocations" (1) failed validation and returned error code 0xC020801C.
End Error
Error: 2007-10-31 10:09:13.85
   Code: 0xC004700C
   Source: 5_Copy vw_Allocations DTS.Pipeline
   Description: One or more component failed validation.
End Error
Error: 2007-10-31 10:09:13.85
   Code: 0xC0024107
   Source: 5_Copy vw_Allocations
   Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started:  10:07:42 AM
Finished: 10:09:13 AM
Elapsed:  91.422 second

 

 

 

Answer 1

Could you make sure dtexec is ran as 32-bit process. Can you try to run it manually from a 32-bit window?

 

Thanks,

Bob

Answer 2

Hi I am having the same Problem.

Can you let me know how? and after checking it what is next?

 

CD

 

Answer 3

Can you look at the output you are getting from DTEXEC (particularly the first couple of lines)? That will tell you if you are running the 32-bit version of DTEXEC.

 

Answer 4

That's What I Get.

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 64-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

At least one of the DTS, SQL, or File options must be specified.

 

Same with when iam trying to run access ODBC Connection.

Here is the error message.

 

An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  Description: "Class not registered".

Error: 0xC00291EC at Append tblExportData to tblExportTransactions, Execute SQL Task: Failed to acquire connection "Microsoft Access". Connection may not be configured correctly or you may not have the right permissions on this connection.

Task failed: Append tblExportData to tblExportTransactions

 

CD

Answer 5

In the 2nd line, you can see that you are running the 64-bit version of DTEXEC. The is no 64-bit JET driver, so you need to run the 32-bit version, usually located at: "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe"

Answer 6

I am having the same problem.  But how the heck to you get SQLAgent to run the 32 bit version of DTExec instead of the 64 bit version?

Answer 7

You have to use a CmdExec step type instead of the Integration Services step type, and specify the 32-bit version of DTEXEC in the command line.

 

Answer 8

jwelch wrote:

You have to use a CmdExec step type instead of the Integration Services step type, and specify the 32-bit version of DTEXEC in the command line.

Thanks jwelch, ya know, I always forget this setting when I'm running an ad hoc report manually from my 64-bit machine in BIDS using Excel as a source.  

Answer 9

If you're running from BIDS, you can also set the Run64BitRuntime property on the project to FALSE (right click the project, click properties, then go to the Debugging page to see the option), and it should work OK.

Answer 10

Thanks! You da man!

Answer 11

 

I did that part in SQL Server 2005 job step with CMDEXEC: (and also set the Runtime64bit to False).

C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe  /f "F:\Missingand_Sln_Packages\MissingDocs.dtsx"  /MAXCONCURRENT " -1 " /CHECKPOINTING OFF  /REPORTING EW

 

Still got the same error.

 

Why is that?  Please help.

 

 

Answer 12

I'm running into the same error for an Oracle connection and I'm using what appears to be the 32 bit version:

 

Cmd = "D:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /SQL "\Package" /SERVER "Servername" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E /DECRYPT password

 

Message = Executed as user: <user>. ....00.3042.00 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  10:04:10 AM  Error: 2008-10-23 10:04:10.78     Code: 0xC0202009     Source: <package> Connection manager "<server>.<instance>.<user>"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040154.  An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  Description: "Class not registered".  End Error  Error: 2008-10-23 10:04:10.78     Code: 0xC020801C     Source: Data Flow Task <taskname> [1]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "<server>.<instance>.<user>" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method cal...  Process Exit Code 1.  The step failed.

 

I had also set the RUN64BITRUNTIME value in the project to false in BIDS before saving, building & deploying.

 

Help!?  Please...

Answer 13

In the first line of your output, you can see you are calling the 64-bit version of DTEXEC. Call it from the Program Files (x86) folder instead.

 

The Run64BitRuntime property only applies when you are running the project through BIDS - it's a project property, not a package property. When running them on the server, you have to call the correct version of DTEXEC.

 

Answer 14

Hi

 

I am having the same problem but I schedule the job to run from the SQL server agent job. How do I change that to 32 bit from the sql server agent. please let me know. thanks!

 

 

Answer 15

In SQL Server 2008, there is an option in the job step properties page to use 32-bit instead of 64-bit. Under 2005, though, the only way to control it is to use a CmdExec step to call the 32-bit version of DTEXEC, as discussed above in this thread.

Answer 16

Even if I use the CmdExec and specify 32 bit version I am getting the same error messsage. Is there anything I need do with OLE DB provider for DB2? please let me know. Thanks!

 

Answer 17

Is it installed?

 

Answer 18

 

I don't know what needs to be installed, I installed SQL 2005 and when I saw in the Program files (86) folder I saw all the folders there like on Program files. Do I need to install anything. I have also installed DB2 version 8 on the server for the OLE DB. But still I am getting the following error message. Please advise me on how to run the SSIS package successfully. Thanks!

 

Date  11/24/2008 4:30:05 PM
Log  Job History (Job Name)

Step ID  1
Server Server Name
Job Name  Job Name
Step Name STep 1

Duration  00:00:40
Sql Severity  0
Sql Message ID  0
Operator Emailed  
Operator Net sent  
Operator Paged  
Retries Attempted  0

Message
Executed as user: User. ...0.3042.00 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  4:30:05 PM  Error: 2008-11-24 16:30:45.21     Code: 0xC0202009     Source: MasterAuto Connection manager "New Connection"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040154.  An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  Description: "Class not registered".  End Error  Error: 2008-11-24 16:30:45.23     Code: 0xC020801C     Source: Load Master Data Master_PBAT [1]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "New Connection" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  End Error  Error: 2008-11-24 16:30:45.23     Code: 0xC0047017     S...  Process Exit Code 1.  The step failed.

Answer 19

 

Do you have the 32-bit or 64-bit driver for DB2 installed?

Answer 20

Thanks for your time, I have installed 64 bit client version for DB2. And SQL server is on 64 bit edition. I have been working on this since two weeks and  could not find any solution. Any help would be appreciated.

 

Answer 21

I have a question about error handling. If you run the job as a integration services package and it fails it will return an error code. You can then set it to notify you that the job failed.

If, however, you run it from the command line you will never get this kind of error message. Even if the integration package fails to run the command line code you executed still has run correctly, therefore it will return a successfully completed message.

Answer 22

still does not work after setting Run64BitRuntime property as false. any more help pls?

Answer 23

Has anyone gotten this to work.  We are running into the same problem whilst trying to connect to oracle.  We can make a connection (test connection) but to run a query we receive the error.  From what I've seen Oracle does not like anything comming from the (86) directory.  We have this same problem running Quest TOAD for Oracle.  The way around it was to start TOAD from command line using the old dos 8 char limit e.g. c:\progra~1\TOAD.EXE  This actually works.

Answer 24

This worked for me. Thanks!

---

The C# n00b

Answer 25

When debugging and If you are running 64 bit
within BIDS

click on project
click on the properties of your project
Under  debugging, set the run64bitruntime to false

Answer 26

This should tell you what you need to know about SSIS and 64-bit support for 32-bit drivers.

For those having difficulty with Oracle and paths - the one suggestion to use "old style" 8.3 pathnames should work, since Oracle apparently dislikes parentheses in paths.  (I've also heard that renaming the "Program Files (x86)" folder to "Program Files x86" also works - but that may break other things.)

---