none
How can i tell if i need 64 or 32 bit runtime on the server running the integration services service ? RRS feed

  • Question

  • Hi

    I have created a SSIS 2012 solution/project. This is a new project - to contain what were SSIS 2008 packages that we have upgraded to SSIS 2012.

    In the project/solution properties (configuration/debugging) the Run64bitruntime  property is set to TRUE - ie when I open it on the development server using SSDT.

    The package that connects to some oracle databases works ok - but we appear to have both 32 and 64bit oracle client installed on the DEV machine - so don't know which is being used,

    On the production  SSIS 2012 server- we have installed Oracle 64 bit client.

    The project has been deployed to the SSIS catalog.

    The SSIS package (in the catalog) has been scheduled to run from a SQL agent job - but the job fails.

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

    ...:Error: The requested OLE DB provider MSDAORA.1 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000.

    Do I need to install the 32 bit Oracle client on the production server ?

    OR is their a way to force SSIS (when running the package in the catalog from SQL agent) to use the 64 bit oracle client ?

    Thanks

    Friday, August 4, 2017 8:56 PM

Answers

  • Well, run your package in 32-bit mode through "execution options" tab in "job step properties". more: SSIS Basics: Running a Package in 32 Bit Mode
    Saturday, August 5, 2017 10:58 PM
  • Thanks everyone.

    I can see from the SQL agent job step properties that I am not using the 32 bit SSIS runtime (the box s not ticked in the advanced job step properties). So I am using 64 bit SSIS. 

    I am sure that I installed 64 bit Oracle client.

    The SSIS package uses the Microsoft Oracle driver - "Native OLE DB\Microsoft OLE DB Provider for Oracle"

    (I can see that another one is possible called "Oracle Provider for OLE DB")

    Is the issue that the Microsoft Driver ("Native OLE DB\Microsoft OLE DB Provider for Oracle") is ONLY 32 bit and thus needs the 32 bit oracle client to work ? 

    See for example https://www.sqlservercentral.com/Forums/Topic1324309-1044-1.aspx

    We should probably switch to the oracle provider - but if we stick with microsoft driver will we need the 32 bit Oracle client ?

    • Marked as answer by goofygdog2 Monday, August 7, 2017 11:04 PM
    Monday, August 7, 2017 9:48 AM
  • I know know that as the developer used the Microsoft OLE DB provider for oracle, this only appears to work with 32 bit so, I needed to

    a) install the 32 bit oracle client

    b) ensure that the SQL agent job uses 32 bit - by going into the advanced TAB of the SQL agent job step properties and ticking the 32 bit box.

    Then the package worked from SQL agent !

    Hurrah!

    • Marked as answer by goofygdog2 Tuesday, August 8, 2017 9:15 AM
    Tuesday, August 8, 2017 9:15 AM

All replies

  • ...:Error: The requested OLE DB provider MSDAORA.1 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000.

    From the error message, it appears that the 32bit driver is installed, but not the 64bit.
    Friday, August 4, 2017 9:11 PM
  • Well, run your package in 32-bit mode through "execution options" tab in "job step properties". more: SSIS Basics: Running a Package in 32 Bit Mode
    Saturday, August 5, 2017 10:58 PM
  • I am apparently using ODAC 11.2 Release 5 and Oracle Developer Tools for Visual Studio (11.2.0.3.20) from http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html connecting to Oracle 9i and 10g.

    Using SSDT_14.0.61021.0_EN.iso SSDT version and VS 2015 update 3.

    In development it seems fine to run on 64 or 32 bit but in production I need to set my job agent to run as 32 bit.

    chanmm


    chanmm

    Sunday, August 6, 2017 12:00 PM
  • Hi Greg Booth,

    The error message tells us that the driver of ORACLE is 32-bit version has been installed, if you would like to run in 64-bit mode, you need install 64-bit version driver of ORACLE.

    Generally, executing SSIS package with which mode depends of the version of driver. In this case, you could either switch run-time to 32-bit mode or install 64-bit version driver of Oracle and check if this issue persist.

    Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by DeviantLogic Monday, August 7, 2017 6:54 AM
    • Unproposed as answer by goofygdog2 Monday, August 7, 2017 9:49 AM
    Monday, August 7, 2017 2:43 AM
    Moderator
  • Thanks everyone.

    I can see from the SQL agent job step properties that I am not using the 32 bit SSIS runtime (the box s not ticked in the advanced job step properties). So I am using 64 bit SSIS. 

    I am sure that I installed 64 bit Oracle client.

    The SSIS package uses the Microsoft Oracle driver - "Native OLE DB\Microsoft OLE DB Provider for Oracle"

    (I can see that another one is possible called "Oracle Provider for OLE DB")

    Is the issue that the Microsoft Driver ("Native OLE DB\Microsoft OLE DB Provider for Oracle") is ONLY 32 bit and thus needs the 32 bit oracle client to work ? 

    See for example https://www.sqlservercentral.com/Forums/Topic1324309-1044-1.aspx

    We should probably switch to the oracle provider - but if we stick with microsoft driver will we need the 32 bit Oracle client ?

    • Marked as answer by goofygdog2 Monday, August 7, 2017 11:04 PM
    Monday, August 7, 2017 9:48 AM
  • I know know that as the developer used the Microsoft OLE DB provider for oracle, this only appears to work with 32 bit so, I needed to

    a) install the 32 bit oracle client

    b) ensure that the SQL agent job uses 32 bit - by going into the advanced TAB of the SQL agent job step properties and ticking the 32 bit box.

    Then the package worked from SQL agent !

    Hurrah!

    • Marked as answer by goofygdog2 Tuesday, August 8, 2017 9:15 AM
    Tuesday, August 8, 2017 9:15 AM
  • Thanks Saeid!

    Also switched to the 32 bit oracle client - and now the job runs from SQL agent.

    Tuesday, August 8, 2017 9:16 AM