none
SSIS Hangs from Oracle connection

    Question

  • Hello there,

    I am having a really frustrating issue when attempting to execute a data flow that using an Oracle 11g database as an OLEDB data source.  When the query from Oracle returns more than 25 records the execution of the SSIS package hangs.  Of course it works perfectly on my development machine, but when I deploy to our test server the data flow step turns yellow and nothing happens.  Please note the package doesn't hang after 25 rows come back, it behaves as if the query never executes.  If I restrict the filter to limit the query to 25 rows (rownum <= 25) the package executes no problem.  Also, when I run the unfiltered query via SQL*Plus on the same machine, it works fine.  A little bit about the environment:

    1) The SQL Server is 2008R2 64 bit enterprise edition on Window Server 2008R2

    2) The oracle machine is 11g on windows server 2008R2

    3) I installed the 32 bit 11g client on the SQL Server box

    4) Everytime I execute the package, I am executing in a 32 bit mode (via visual studio I choose: Run64BitRunTime=False, via SQL Agent Jobs I choose: Execution Options Use 32 bit runtime, via dtexec I use /X86 switch.) 

    Like I said, this works fine on my machine which is 64bit windows 7 with the same 32bit 11g client.

    Via dianostic debugging, this is the last entry in dbo.sysssislog when it hangs: ExternalRequest_post: 'ICommandWithParameters::GetParameterInfo succeeded'. The external request has completed.

    Any help is appreciated, I have run through the similiar forums posts with no luck from their suggestions:

    http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/f9f23e11-8105-451b-adcf-d944064d3c67/

    http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/dff5d3a0-55fa-45e0-ae4e-fd85973f1754

    thanks,

    CK


    • Edited by CR8N Monday, April 09, 2012 5:13 PM
    Monday, April 09, 2012 4:17 PM

Answers

  • Ok,

    I found out the answer, this was a doozy and required help from a network admin who understands Oracle.  I had to change a option on my client tnsnames.ora file to specify SDU=1400.  SDU is the session data unit specifies the size of the packets that Oracle sends when transmitting data.  The way it was explained to me is that if your SDU is not specified, the default size (2408) might be greater than your routers maximum transmission unit (MTU) which can lead to packet loss.  I changed the value in my tnsnames.ora file right under the description:

    ORACLESERVER =

      (DESCRIPTION =
    (SDU=1400)
        (ADDRESS = (PROTOCOL = TCP)(HOST = machine.network.com)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = ARMS)
        )
      )

    That solved the problem.  By the way, I love SQL Server more than ever now.

    thanks,

    CK


    • Edited by CR8N Wednesday, April 11, 2012 2:31 PM
    • Marked as answer by CR8N Wednesday, April 11, 2012 2:31 PM
    Wednesday, April 11, 2012 2:30 PM

All replies

  • What are you doing in the piece where the package gets stuck?

    In my experience, either there is an error (perhaps even graphical) that needs an attention or some kind of an OLEDB one.

    Perhaps even there is an error on the Oracle side.

    Also when you say only 25 records work, then let's see what is coming in the 26 onward.


    Arthur My Blog

    Monday, April 09, 2012 5:30 PM
  • Hi,

    The slowness of the retrival of the rows from oracle depends upon various factor:

    1: Check the optimization of the oracle query.

    2: Check the "Default Buffer Max Rows", properties of the data flow task.

    If it was 25 then changed it to min 1000 rows.

    3: You can use Microsoft SSIS Oracle Connector - Attunity for pulling data from oracle source.

    Pulling data using Attunity will increase the retrival speed by 25% as compared to normal OLEDB connection.

    Reards

    Sudipta Ghosh


    Sudipta Ghosh

    Monday, April 09, 2012 5:34 PM
  • Thanks for the reply Arthur,

    The package gets stuck on the initial query to pull data from Oracle, after that point it is going to a rowcount transformation.  There is no error logged by SSIS that I have seen, I am doing diagnostic logging and there is no records in the dbo.sysssislog table for the onerror or onwarning events.  When I run the package in BIDS, there are no errors or warnings in the progress tab.  The data flow task turns yellow and nothing happens for the OLE DB data source.

    When I limit the query to 25 records, the packages work fine.  The hang happens when I set the query to go to anything > 25  records.  It simply doesn't start the OLE DB Source in the data flow.

    thanks,

    CK

    Monday, April 09, 2012 6:31 PM
  • So I assume, in the DFT the 1st task is an Execute SQL Query that does something like SELECT abc from MyOracleTable. Is that correct?

    When you say you permit it to access say 30 rows by doing SELECT top 30 FROM MyOracleTable then the step turns yellow, right?


    Arthur My Blog

    Monday, April 09, 2012 6:54 PM
  • Yes, the first operator in the DFT is a OLE DB Source with a query that says "SELECT COL1, COL2 FROM TABLE WHERE ROWNUM <= 25"

    The packages executes fine when the row count restriction is <= 25.  After

    When I change the restriction in the where clause to any value > 26, the DFT turns yellow, but the OLE DB Source doesn't change color. 

    The last record in the dbo.sysssislog table is this entry "ExternalRequest_post: 'ICommandWithParameters::GetParameterInfo succeeded'. The external request has completed."

    Like I said previously, the query works fine via SQL*PLUS and with Oracle Toad, just not through SSIS.  Really strange behavior.

    thanks,

    CK

    Monday, April 09, 2012 7:18 PM
  • How many rows are there?

    I am thinking it is just keeping pulling the data.

    What if you try "SELECT COL1, COL2 FROM TABLE WHERE ROWNUM <= 1025"


    Arthur My Blog

    Monday, April 09, 2012 7:21 PM
  • The table has 46 records.  I don't think that is the problem, I can pull every record from tables with hundreds of thousands of records on my local machine.  I just can't do it on the server.  Any number above 25 hangs.

    thanks,

    CK

    Monday, April 09, 2012 8:16 PM
  • Did you monitor the Oracle side? What happens there?

    Network packet size is the next item I would suggest looking into. Just make it twice as large (follow http://msdn.microsoft.com/en-us/library/ms177437%28v=sql.100%29.aspx is you need to know how).


    Arthur My Blog

    Monday, April 09, 2012 8:27 PM
  • Yes, I did monitor the Oracle side.  When I run it locally, I can see the queries running in the Oracle server.  When I run it from the server I don't see any connection made,  unless I put on the 25 record restriction.

    I doubled the network packet size with no effect.  Just curious, why would that matter, ssis is in a different process.

    thanks,

    CK

    Monday, April 09, 2012 9:07 PM
  • Hi CK,

    Please the to Microsoft Connector for Oracle by Attunity with SQL Server 2008 Integration Services, for more details about how to use it, please see: http://technet.microsoft.com/en-us/library/ee470675(v=sql.100).aspx 

    And here is a similar thread, please refer to: http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/40c27bad-3161-4305-93aa-9ed27e6776ce 

    Thanks,
    Eileen

    TechNet Subscriber Support

    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

    Tuesday, April 10, 2012 9:03 AM
  • Hi Eileen,

    That post isn't really a similiar problem.  Performance isn't the issue I am facing, I can't get results from a table with 46 records.  I have tried the Attunity connectors to no avail.  I have involved the network admins to see if this is something network/firewall related.  I'll post the findings.

    thanks,

    Creighton

    Tuesday, April 10, 2012 3:31 PM
  • Ok,

    I found out the answer, this was a doozy and required help from a network admin who understands Oracle.  I had to change a option on my client tnsnames.ora file to specify SDU=1400.  SDU is the session data unit specifies the size of the packets that Oracle sends when transmitting data.  The way it was explained to me is that if your SDU is not specified, the default size (2408) might be greater than your routers maximum transmission unit (MTU) which can lead to packet loss.  I changed the value in my tnsnames.ora file right under the description:

    ORACLESERVER =

      (DESCRIPTION =
    (SDU=1400)
        (ADDRESS = (PROTOCOL = TCP)(HOST = machine.network.com)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = ARMS)
        )
      )

    That solved the problem.  By the way, I love SQL Server more than ever now.

    thanks,

    CK


    • Edited by CR8N Wednesday, April 11, 2012 2:31 PM
    • Marked as answer by CR8N Wednesday, April 11, 2012 2:31 PM
    Wednesday, April 11, 2012 2:30 PM