locked
Mystery about using SSIS 2012 connecting to Oracle 11G RRS feed

  • Question

  • hi folks:

      This seems to be a very straightforward but it is actually not. 

    background info:

      I am using my local machine (64bit Windows 7 Pro) as the dev box. I've downloaded the Oracle 11G Client for 32bit because I was told that SSIS 2012 SSDT is using 32bit.

    After 32bit oracle client installed, I've created a SSIS package connecting to a oracle 11G table and did normal PLSQL work . 

    When I executed this package inside the SSIS 2012, it worked like a chime. 

    Question 1: 

      Do I absolutely have to install Oracle 11G Client for 32bit as I only need the oracle Provider for OLE DB as client will install other softwares which I do not need at all. Can Oracle Data Access Components also do the same work?

    Next, things are not done yet. Once my ssis package passed the test inside the ssis 2012 ( light version of Visual studio), I 

    created a sql job on my local machine and created SSIS step pointing to this package. The execution options is using default value which means option [Use 32bit runtime] is unchecked. Guess what happened?  it seems this job just got stuck there ... no errors but always running... This is a simple ssis package and it should be finished within  10 seconds . 

    Question 2: 

       1. What happened ? Since I am using SQL Agent to execute this SSIS package, do I need to install the 64bit oracle client or ODAC as SQL agent is supposed to be 64bit? 

       2. If I do need to install the 64bit Oracle client in order to make SQL job work, I will end up with two versions of Oracle client in the same box, how would that work? 

    Thanks

     Hui



    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

    Wednesday, November 18, 2015 7:31 PM

Answers

  • Hi cat_ca,

    You need to install the proper Oracle Client.

    You need to enable the 32 Bit mode or also install the Oracle client 64 bit.


    Arthur

    MyBlog


    Twitter

    • Marked as answer by cat_ca Friday, November 20, 2015 6:48 PM
    Wednesday, November 18, 2015 7:40 PM
  • Thanks ArthurZ. I got it work after installing 64bit ODAC to the Prod SSIS server and sql server job with ssis step did run with success. However, this comes with a glitch that I will never be able to open the deployed SSIS package on the target server using visual studio  since VS requires a 32bit ODAC but only 64bit ODAC installed on the prod server. 


    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

    Friday, November 20, 2015 6:39 PM

All replies

  • Hi cat_ca,

    You need to install the proper Oracle Client.

    You need to enable the 32 Bit mode or also install the Oracle client 64 bit.


    Arthur

    MyBlog


    Twitter

    • Marked as answer by cat_ca Friday, November 20, 2015 6:48 PM
    Wednesday, November 18, 2015 7:40 PM
  • hi Authur: 

      I did end up with the Oracle Client 64Bit.  My dev box actually contained both 32bit and 64bit.  When the Sql Agent runs, which version is running?  When I run the package inside SSDT 2012, it run with success. 

     any thoughts? 


    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

    Wednesday, November 18, 2015 7:48 PM
  • You can set to 32 but it run in 64 bit mode by default

    BIDS and SSDT are desktop apps that are 32 bit only.

    64 bit is preferred on a server machine


    Arthur

    MyBlog


    Twitter

    Wednesday, November 18, 2015 8:51 PM
  • Thanks ArthurZ. I got it work after installing 64bit ODAC to the Prod SSIS server and sql server job with ssis step did run with success. However, this comes with a glitch that I will never be able to open the deployed SSIS package on the target server using visual studio  since VS requires a 32bit ODAC but only 64bit ODAC installed on the prod server. 


    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

    Friday, November 20, 2015 6:39 PM
  • You are welcome,

    Having a VS on a production machine is not a good idea, I consider it a security breach even, always export the package and open locally.


    Arthur

    MyBlog


    Twitter

    Friday, November 20, 2015 6:45 PM