英文:
how to set timeout to oracleclient connection in SSIS?
问题
我有一个从Oracle导入数据到SQL的SSIS包。此外，还有一个SQL作业，每5分钟执行一次此包。问题是，当SSIS无法建立与Oracle的连接时，没有超时错误可以导致整个包失败。因此，作业保持在执行状态，SSIS尝试建立连接，甚至在几天后仍然尝试。关键问题是，Oracle不会在连接尝试失败一次后允许SSIS连接，无论连接问题是否已解决。
如何防止这个问题？是否有一种方式可以在连接不成功时使作业失败，以便在失去数据5分钟后重新执行作业并建立新的连接？
只是提醒大家，我在SSIS中设置了OracleClient连接类型。SQLClient连接类型已经有一个设置连接超时的选项。
英文:
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.
答案1
得分: 0
尝试在脚本任务中包装Oracle连接：不要直接在数据流中使用Oracle连接管理器，而是使用脚本任务来以编程方式建立连接。这样，您将对连接过程拥有更多控制权。
在脚本任务中，您可以使用Oracle Data Provider for .NET（ODP.NET）中的OracleConnection类来设置连接超时机制。OracleConnection类具有ConnectionTimeout属性，您可以使用它来设置建立连接的超时值。
在执行SSIS包的SQL Server作业中，您可以配置它以适当处理故障。设置作业在一定数量的失败后重试包执行，或者在一定数量的重试后失败作业。如果SSIS包无法连接到Oracle，作业将在定义的时间段后重试。
以下是示例C#代码，用于在脚本任务中建立Oracle连接和设置连接超时：
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()
{
// 设置Oracle连接字符串
string connectionString = "your_oracle_connection_string";
// 创建OracleConnection对象
using (OracleConnection connection = new OracleConnection(connectionString))
{
// 设置连接超时值（以秒为单位）
connection.ConnectionTimeout = 300;
try
{
// 打开连接
connection.Open();
// 连接成功后，在此处继续数据导入逻辑
// ...
}
catch (Exception ex)
{
// 连接失败时，根据您的需求处理异常
// ...
}
}
Dts.TaskResult = (int)ScriptResults.Success;
}
}
}
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论