locked
Random Connection Failure to Oracle from Packages RRS feed

  • Question

  • I have a project with 18 packages in it that connect to an Oracle database to extract data from over 150 Oracle tables. Sometimes the project completes okay; sometimes it doesn't.

    The 18 packages have two to four data flow steps executed one at a time, with each step extracting data from four to eight Oracle tables simultaneously and depositing the rows into SQL Server tables.

    When it errors, it is always the same: "ORA-12170: TNS:Connect timeout occurred".  The error could be in package 12 one night and in package 18 another night. Sometimes the project runs successfully through all 18 packages for two or three nights.

    Our network and Oracle people say that when it times out, they notice that Oracle sends a "connection okay" message, but there is no response from the SQL Server side. Then it continues to try to send the "connection okay" for about 30 seconds, upon which it times out.

    Has anyone seen this problem before?

    Greg

    Monday, December 29, 2014 7:35 PM

Answers

  • The Attunity driver is a completely different thing.  I would suggest contacting them and see if there is any diagnostic data they can provide.

    Also see:

    http://www.attunity.com/forums/microsoft-ssis-oracle-connector/time-out-error-1576.html


    PS.  The error "ORA-12170" is actually coming from the driver.  So it is not technically an SSIS issue, but something else.
    Tuesday, December 30, 2014 6:40 PM
  • Greg,

    If you cannot solve the problem with the driver (Attunity) consider using package checkpoints. You can restart the package from the point of failure then. More info:

    http://msdn.microsoft.com/en-us/library/ms140226.aspx

    Tuesday, December 30, 2014 8:03 PM

All replies

  • Since it is random and the same package works again without modification, it implies an actual timeout.  This can be caused by many variables, on the SQL Server, network, or Oracle.

    I would start by editing the connection string and adding "Connection Timeout=30" to increase the timeout from 15 to 30 seconds.

    See how that works.

    http://docs.oracle.com/cd/B14117_01/win.101/b10117/features001.htm

    Monday, December 29, 2014 7:55 PM
  • Tom, it looks like the only way to change the connection string is to enter it into a configuration table, is that correct? If I bring up the properties of the connection manager and make a change to enter the timeout, it doesn't save it. If I click the ellipsis button to edit the connection string, there is no option for changing the timeout.


    Oh, by the way, we use Attunity.
    • Edited by Greg Coles Tuesday, December 30, 2014 6:14 PM
    Tuesday, December 30, 2014 4:59 PM
  • The Attunity driver is a completely different thing.  I would suggest contacting them and see if there is any diagnostic data they can provide.

    Also see:

    http://www.attunity.com/forums/microsoft-ssis-oracle-connector/time-out-error-1576.html


    PS.  The error "ORA-12170" is actually coming from the driver.  So it is not technically an SSIS issue, but something else.
    Tuesday, December 30, 2014 6:40 PM
  • Greg,

    If you cannot solve the problem with the driver (Attunity) consider using package checkpoints. You can restart the package from the point of failure then. More info:

    http://msdn.microsoft.com/en-us/library/ms140226.aspx

    Tuesday, December 30, 2014 8:03 PM