SSIS包 - 循环遍历多个数据源。 [英] SSIS Package - Loop through multiple data sources.

问题描述

我们有一个SSIS包，可以从多个数据源中提取数据。

We have a SSIS package that pulls data from multiple data sources.

所以，ssis包有 执行SQL任务，该任务提供要用作数据源的SQL Server列表。

So, the ssis package has  Execute SQL task that feeds the list of SQL Servers to be used a data sources.

第二步是For Each Loop，它具有数据流任务。源连接管理器是基于表达式的服务器名称作为变量。 

The second step is For Each Loop that has data flow task. The Source Connection manager is expression based with servername as variable. 

这个工作正常唯一的问题是，如果数据源不可用，我不希望包失败但是而是记录该错误并继续处理其他数据源。

This works fine the only issue is, if the data source is not available, I do not want the package to fail but rather log that error and continue processing other data sources.

任何想法如何使包继续处理其他数据源而不会使包失败，如果其中一个不可访问。

Any thoughts how to make the package continue processing other data source without failing the package, if one of them is not accessible.

希望它能帮助!!

推荐答案

嗨Stan， 

Hi Stan, 

如果数据源不可用，我不希望包失败，而是记录该错误并继续处理其他数据源。

然后，您可以在数据流任务之前添加脚本任务，以检查连接是否可用并将状态写入变量（ vConnSuccess）。并将vConnSuccess映射到优先约束到数据流任务的表达式。 

Then you can add a Script Task before the Data Flow Task, to check if the connection is available and write the status into variable(vConnSuccess). And map vConnSuccess to the expression in precedence constraint to Data Flow Task. 

这样，如果连接无效，脚本任务将记录消息，并跳到下一个循环。 

In this way, if the connection is not valid, the script task will log the message, and skip to the next loop. 

请参阅  使用前确认连接[SSIS]

Please see Verify a connection before using it [SSIS]

在您的情况下，代码如下： 

In your case, the code would be like this: 

 public void Main()
        {
            bool fireAgain = true;
            ConnectionManager cm = Dts.Connections["ConnectionManagerName"];
                Dts.Events.FireInformation(1, "", String.Format("ConnectionManager='{0}', ConnectionString='{1}'",
                    cm.Name, cm.ConnectionString), "", 0, ref fireAgain);
                try
                {
                cm.AcquireConnection(null);
                    Dts.Events.FireInformation(1, "", String.Format("Connection acquired successfully on '{0}'",
                        cm.Name), "", 0, ref fireAgain);
                    Dts.Variables["vConnSuccess"].Value = true;
                }
                catch 
                {
                Dts.Events.FireInformation(0, "", String.Format("Connection acquired failed on '{0}'",
                    cm.Name), "", 0, ref fireAgain);
                Dts.Variables["vConnSuccess"].Value = false;
                    Dts.TaskResult = (int)ScriptResults.Success;
                }
            
                Dts.TaskResult = (int)ScriptResults.Success;
        }

这篇关于SSIS包 - 循环遍历多个数据源。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！