how to set timeout to oracleclient connection in SSIS?

Question

I have an SSIS package that imports data from Oracle to SQL. also, there is a SQL job that executes this package every 5 minutes. the problem is when SSIS fails to establish a connection to oracle there is no timeout error that can fail the whole package. Therefore, the job stays in the executing state and SSIS keeps trying to establish a connection even after several days. the point is that Oracle won't let SSIS to connect after only one unsuccessful try for connection, no matter if the connection problem is resolved or not.

How can I prevent this problem? is there any way to fail the job when connection is unsuccessful so after a loss of data for 5 minutes the job will be executed again and a new connection would be established?

Just to remind you guys, I'm setting an OracleClient connection type in SSIS. The SQLClient connection type already has an option for setting timeout to connection.

Answer 1

Try to wrap the Oracle connection within a script task: Instead of directly using the Oracle connection manager in the data flow, use a script task to establish the connection programmatically. This way, you will have more control over the connection process.

Within the script task, you can set a connection timeout mechanism using the OracleConnection class from the Oracle Data Provider for .NET (ODP.NET). The OracleConnection class has a ConnectionTimeout property that you can use to set the timeout value for establishing a connection.

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using Oracle.ManagedDataAccess.Client;

namespace ST_XXXXXXXXXXXXXXXXXXXXXXXX
{
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            // Set the connection string for Oracle
            string connectionString = "your_oracle_connection_string";

            // Create an OracleConnection object
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                // Set the connection timeout value (in seconds)
                connection.ConnectionTimeout = 300; 

                try
                {
                    // Open the connection
                    connection.Open();
                    
                    // Connection successful, proceed with data import logic here
                    
                    // ...
                }
                catch (Exception ex)
                {
                    // Connection failed, handle the exception as per your requirements
                    
                    // ...
                }
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

In the SQL Server job that executes the SSIS package, you can configure it to handle failures appropriately. Set the job to retry the package execution after a certain number of failures or to fail the job after a specific number of retries. If the SSIS package fails to connect to Oracle, the job will be retried after a defined period.