none
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

     

     

     

    Wednesday, October 31, 2007 1:40 AM

Answers

  • 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.

    Wednesday, March 19, 2008 10:15 PM
    Moderator
  • 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"

    Friday, February 01, 2008 6:37 PM
    Moderator
  • 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.

     

    Thursday, March 06, 2008 10:18 PM
    Moderator

All replies

  • 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

    Wednesday, October 31, 2007 7:13 AM
    Owner
  • Hi I am having the same Problem.

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

     

    CD

     

    Friday, February 01, 2008 5:08 PM
  • 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.

     

    Friday, February 01, 2008 6:13 PM
    Moderator
  • 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

    Friday, February 01, 2008 6:21 PM
  • 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"

    Friday, February 01, 2008 6:37 PM
    Moderator
  • 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?

    Thursday, March 06, 2008 9:58 PM
  • 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.

     

    Thursday, March 06, 2008 10:18 PM
    Moderator
  •  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.  
    • Proposed as answer by Learning1 Wednesday, April 25, 2012 7:27 PM
    Wednesday, March 19, 2008 10:09 PM
  • 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.

    Wednesday, March 19, 2008 10:15 PM
    Moderator

  • Thanks! You da man!
    Thursday, March 20, 2008 12:05 AM
  •  

    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.

     

     

    Tuesday, August 26, 2008 8:19 PM
  • 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...

    Thursday, October 23, 2008 3:34 PM
  • 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.

     

    Thursday, October 23, 2008 4:24 PM
    Moderator
  • 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!

     

     

    Thursday, November 20, 2008 4:05 PM
  • 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.

    Thursday, November 20, 2008 10:38 PM
    Moderator
  • 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!

     

    Monday, November 24, 2008 9:35 PM
  • Is it installed?

     

    Tuesday, November 25, 2008 2:30 AM
    Moderator
  •  

    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.

    Tuesday, November 25, 2008 3:24 PM
  •  

    Do you have the 32-bit or 64-bit driver for DB2 installed?
    Tuesday, November 25, 2008 3:47 PM
    Moderator
  • 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.

     

    Tuesday, November 25, 2008 8:08 PM
  • 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.

    Friday, January 30, 2009 4:01 PM
  • still does not work after setting Run64BitRuntime property as false. any more help pls?

    Wednesday, September 02, 2009 1:05 PM
  • 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.

    Friday, November 06, 2009 2:48 PM
  • This worked for me. Thanks!
    The C# n00b
    Wednesday, February 10, 2010 9:48 PM
  • 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
    Friday, February 26, 2010 11:59 PM
  • 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.)
    Todd McDermid's Blog
    Saturday, February 27, 2010 1:57 AM
    Moderator