none
SSIS Problem->SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154.

    Question

  • We had a SQLServer Agent/SSIS Job scheduled and running on a test server and everything was fine until we created a script to recreate it for Production from the Microsoft SQL Server Management Studio (right click on the job->Script Job As->Create To).

     

    As soon as we had the job scripted, we dropped the job in test and Recreated it in test.

    Of course, now we can't get the thing to run.

    Yell

    Here's some of the error's we're getting:

     

    Microsoft (R) SQL Server Execute Package Utility
    Version 9.00.5000.00 for 32-bit
    Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
    
    Started:  3:40:01 PM
    Error: 2011-09-15 15:40:05.77
       Code: 0xC0202009
       Source: USDecoratedCatalogGroup Connection manager "SourceConnectionOLEDB"
       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: 2011-09-15 15:40:05.77
       Code: 0xC020801C
       Source: Data Flow Task Source - Query [1]
       Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" 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: 2011-09-15 15:40:05.77
       Code: 0xC0047017
       Source: Data Flow Task DTS.Pipeline
       Description: component "Source - Query" (1) failed validation and returned error code 0xC020801C.
    End Error
    Error: 2011-09-15 15:40:05.77
       Code: 0xC004700C
       Source: Data Flow Task DTS.Pipeline
       Description: One or more component failed validation.
    End Error
    Error: 2011-09-15 15:40:05.77
       Code: 0xC0024107
       Source: Data Flow Task 
       Description: There were errors during task validation.
    End Error
    DTExec: The package execution returned DTSER_FAILURE (1).
    Started:  3:40:01 PM
    Finished: 3:40:05 PM
    Elapsed:  3.89 seconds
    

    Now, I can still run these .dtsx packages fine on my local development machine, however as soon as I copy them to the test box, and attempt to run them from the SQL Server Job Activity Monitor, it throws the errors above.

    I've read in some posts on this problem that there is a 64 or 32 bit setting problem, however not sure how to check that.

    Thanks... Doug

    Friday, September 16, 2011 2:25 PM

Answers

  • The only way I was able to fix this was to create new connections for each package.

    We were using 2 for each of 8 packages.

    One for the database connection

    One for the Excel destination file.

    Thanks everyone.

     

    Doug

    • Marked as answer by drdexter33 Tuesday, September 20, 2011 5:26 PM
    Tuesday, September 20, 2011 5:26 PM

All replies

  • Hi,

    "Class not registered" usually means that an activex control in not registered. Make sure you have installed the OLE DB Provider on the server.

    See following blog for more details

    http://blogs.msdn.com/b/pram/archive/2009/03/31/error-class-not-registered.aspx


    Cheers,

    Zaim Raza

    Friday, September 16, 2011 2:41 PM
  • well, again, this was working properly on the server for weeks.

     

    When we dropped and recreated the package is when the problems started.

     

    So, I don't think it has anything to do with the OLEDB Provider.


    • Edited by drdexter33 Friday, September 16, 2011 3:13 PM
    Friday, September 16, 2011 3:13 PM
  •  

    could you please check the latest service pack of sql server with latest cumulative updated on the SSIS box.

     

    Friday, September 16, 2011 3:40 PM
  • Hi drdexter33,

    Thanks for your question and Zaim’s reply. When you execute your package using DTExec, you have the option of 32-bit or 64 bit operation, but the default on a 64-bit installation is to use 64-bit mode. However, some commonly used objects in SSIS don’t have 64-bit counterparts, and will therefore cause your packages to fail.

     

    Todd’s blog has the detail information about this problem and the correspond resolution, please refer to: http://toddmcdermid.blogspot.com/2009/10/quick-reference-ssis-in-32-and-64-bits.html

     

    Thanks,
    Sharp Wang

    TechNet Subscriber Support in forum

    If you have any feedback on our support, please contact us.


    Please remember to mark the replies as answers if they help you and unmark them if they provide no help.
    • Edited by Sharp WangModerator Monday, September 19, 2011 5:49 AM
    • Marked as answer by drdexter33 Tuesday, September 20, 2011 5:25 PM
    • Unmarked as answer by drdexter33 Tuesday, September 20, 2011 5:25 PM
    Monday, September 19, 2011 5:48 AM
  • The only way I was able to fix this was to create new connections for each package.

    We were using 2 for each of 8 packages.

    One for the database connection

    One for the Excel destination file.

    Thanks everyone.

     

    Doug

    • Marked as answer by drdexter33 Tuesday, September 20, 2011 5:26 PM
    Tuesday, September 20, 2011 5:26 PM