none
SSIS Pkg Created in SSMS using Wizard Fails when running it in SSIS

    Question

  • First-time user of ISS in SQL 2008 DevEd,

    Built package using (starting with) import export wizard to extract table data from Access 2003 MDB into SQl 2008 tables.

    I was able to successfully run (in SSMS) the import export package created using the wizard.  The results were as expected

    The package above was saved in the file system

    I then went into SSIS and opened up that saved package (which had been created in/from the wizard).  I ran it and it failed at the first dataflow task.  Preceding that task were drops and creates in the destination DB; those drops and creates were all successful.

    I can double click on the dataflow task and click on preview and see the source data.  However, the dataflow task is not getting the source data into the target tables (even though the very same package, unchanged, worked in SSMS).

    execution Results messages around the failure:

    Task Data Flow Task 1
    Validation has started
    [Source 1 - Band [49]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0209303.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.
    [SSIS.Pipeline] Error: One or more component failed validation.
    Error: There were errors during task validation.
    Validation is completed
    Finished, 11:49:38 AM, Elapsed time: 00:00:00.032
    [Connection manager "SourceConnectionOLEDB"] Error: 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".
    Finished, 11:49:38 AM, Elapsed time: 00:00:00.296

    Only help I can find in this forum was a message with respect to something cryptically called "SNAC". 

     

    I am very much a neophyte in this SQL Server stuff, so be tender!

    How do I make this package run without this error cropping up?

     

     


    RRR
    Tuesday, July 20, 2010 4:56 PM

Answers

  • Reply to self:

    After hours of searching, I found what seemed to be the problem.  I do not clearly understand the problem, but it seems that, when running a package in SSMS, there is no such problem.  Running it with SSIS, the problem seems to be a glitch between the 64 bit SSIS OLE DB providers and the provider for Access DBs being 32 bit.

    The solution - inelegant, I am sure - was to go into the SSIS project's properties page and, under debug options, set the "Run64BitRuntime" property to false. Having done that, saved the project, and running BUILD again, the package runs to a successful completion. 


    RRR
    • Marked as answer by ROBSR Tuesday, July 20, 2010 6:44 PM
    Tuesday, July 20, 2010 6:43 PM

All replies

  • [Connection manager "SourceConnectionOLEDB"] Error: 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".
    Finished, 11:49:38 AM, Elapsed time: 00:00:00.296


    Read your error message closely. It doesn't like the OLE DB provider you have for your Source (Access) database. Was the server using 64 bit drivers and you are using 32 bit? Do you have Access installed on your workstation? Try adding a new Connection Manager by right-clicking on the Connection Manager tray and see if you can set one up that will replace the one added by the wizard.


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    Tuesday, July 20, 2010 6:09 PM
  • Reply to self:

    After hours of searching, I found what seemed to be the problem.  I do not clearly understand the problem, but it seems that, when running a package in SSMS, there is no such problem.  Running it with SSIS, the problem seems to be a glitch between the 64 bit SSIS OLE DB providers and the provider for Access DBs being 32 bit.

    The solution - inelegant, I am sure - was to go into the SSIS project's properties page and, under debug options, set the "Run64BitRuntime" property to false. Having done that, saved the project, and running BUILD again, the package runs to a successful completion. 


    RRR
    • Marked as answer by ROBSR Tuesday, July 20, 2010 6:44 PM
    Tuesday, July 20, 2010 6:43 PM
  • Hay

    Your response is not connected to the subject.

    I would like to confirm, that there is a problem with Microsoft data provider for Oracle!

    I received the same message:

    [Source - NSIM_N_PROJEKTY [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0209303.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

    and finally

    [Connection manager "SourceConnectionOLEDB"] Error: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR.  The requested OLE DB provider MSDAORA.1 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".

    The problem has been solved as described by ROBSR. Many thanks for him

    Friday, April 1, 2011 1:27 PM
  • I experienced the same problem and it makes sense because I installed the 32 bit version of access on that machine (Microsoft suggests this is the preferred).

    Your answer saved me a great deal of time.

    Thursday, April 28, 2011 3:09 PM
  • Thanks!  Because you spent hours of searching, I only had to search for one minute to find your answer.  That solution worked for me too.
    Thursday, May 19, 2011 11:17 PM
  • Hello Kristoforoz,

     I am not quite understanding your participation in this thread:
    "
    Your response is not connected to the subject.
    It looks like ROBSR is answering his own question, since Microsoft comes up short on the issue of running 32-bit applications on 64-bit platforms.  I agree with your remark: we should thank ROBSR for finding this "hack" to run SSIS applications in 64-bit servers.  I have changed the "Run64BitRuntime" property on packages to make them work.  You also can change the execution parameter to make an SSIS package work in SQL Server Agent Service, scheduled jobs.  It is unfortunate that Microsoft cheerleads the 64-bit infrastructure, but dumps on developers by designing CLIENT applications, like Visual Studio, that are 64-bit clueless.  But we cannot blam ROBSR for that.

    Now, your other topic:
    "
    I would like to confirm, that there is a problem with Microsoft data provider for Oracle!"
    I am not sure how this is related to the original thread.  But at least Oracle provides a 64-bit client for Microsoft SQL Server, since Microsoft declines to support their server products with 64-bit clients.  I have installed this, and successfully developed Microsoft SSIS packages against 64-bit Oracle data sources.  Now, if we could just get Microsoft to develop in 64-bit for their OWN data sources!

    Thanks to everyone's input on 64-bit database development. The forums are filling in where Microsoft comes up short.
    Tuesday, October 4, 2011 5:29 PM
  • I have experienced the same problem as ROBSR, with an MS-Access OLE DB Destination. I tried using the ADO.NET solution, but to no avail, I still had to use the debugging tip suggested by ROBSR. However as long as your package runs in the business intelligence studio this will work , but I am afraid it will not work when I will deploy my package on a64 bit server and try to run it from the ssis service alone.
    Jan D'Hondt - Database and .NET development
    Thursday, October 6, 2011 9:32 AM
  • That worked excellent! MS Access 2003 to SQL Server 2008 R2

    Make sure you use OLE DB as both the source and the Destination!

    Tuesday, March 6, 2012 2:52 AM
  • jandho,

    I'm a little late to the party, but for anyone else searching for a solution to this issue, you can execute the package from a Sql Server Agent job and select "Use 32-bit runtime" in the "Execution options" tab of the job step. This will allow the package to run correctly on a 64-bit server.

    Jeff

    Monday, January 7, 2013 3:00 PM
  • Actually, the correct answer is to install the 64-bit version of Microsoft Access Database Engine 2010 Redistributable, and specify Microsoft.ACE.OLEDB.12.0 in your SSIS config file for the Access connection.
    Thursday, September 4, 2014 7:44 PM