SSIS遍历服务器并处理失败的登录 [英] SSIS Looping through servers and handling failed logins

问题描述

创建一个程序包，该程序将遍历我们不同环境(DEV，UAT，PROD)中的所有服务器.我们使用服务帐户，DEV和UAT有一个服务帐户，而PROD有一个服务帐户.我正在使用ForEach循环容器/变量来设置连接字符串.

Creating a package that will loop through all the servers in our different environments(DEV, UAT, PROD). We use service accounts and there is one service account for DEV and UAT and another for PROD. I am using a ForEach Loop Container/variables to set the connection string.

问题:当循环中的数据流尝试连接到PROD服务器时，它们将失败，因为它们使用的是DEV/UAT服务帐户，该帐户显然无权访问PROD，这很好.问题在于这会杀死循环.通常，我只是将事件处理程序放在数据流上，并设置事件处理程序的系统变量Propagate:OnError = False，以使错误不会冒泡并杀死循环.好吧，这是行不通的，因为数据流内部的OLE DB连接在验证期间(程序包正在运行时)失败，并且显然，Propagate = False设置仅防止错误在执行任务期间发生，而不会冒泡.任务验证.

The issue: When the data flow in the loop trys to connect to the PROD servers they fail because they are using the DEV/UAT service account which obviously doesnt have access to PROD, which is fine. The problem is this kills the loop. Normally I would just put an event handler on the data flow and set the event handler's System Variable Propagate:OnError = False so that the error doesn't bubble up to the loop and kill it. Well this doesn't work because the OLE DB connection inside the data flow fails during validation(while the package is running) and apparently the Propagate = False setting only keeps an error from bubbling up if it occurs during the execution of a task and not the validation of a task.

我可以在包括软件包本身在内的所有内容上将MaximumErrorCount设置为0，但这有点繁琐，无论遇到什么错误，软件包都会始终报告其成功运行.

I can set the MaximumErrorCount = 0 on everything including the package itself but that is a bit heavy handed and the package would always report that it ran successfully no matter what errors were encountered.

运行SQL Server 2008-R2

Running SQL Server 2008-R2

链接上一篇文章，介绍如何在不熟悉的情况下使用传播"设置杀死循环.

Link to an article on how to not kill your loop using the Propagate setting if someone isn't familiar with it.

推荐答案

一个建议是将脚本任务放在数据流任务之前，该任务使用try-catch块检查对连接字符串的访问并在失败时设置变量，然后在条件拆分中使用该变量来确定是运行数据流还是记录连接字符串是否失败.

One suggestion would be to put a Script Task before the Data Flow tasks that checks access to the connection string with a try-catch block and sets a variable upon failure, and then use that variable in a conditional split to determine whether to run the Data Flow or log that the connection string failed.

或者，如果您不关心失败的原因(因为您已经知道是由于权限的原因)，则可以使用优先约束，并且仅在连接成功的情况下运行数据流.

Alternatively, if you don't care about why it failed (since you already know it's because of permissions) you could just use a Precedence Constraint and only run Data Flows where the connection succeeded.

更新:

以下是脚本任务的一些工作代码:

Here's some working code for the Script Task:

 public void Main()         {

string connString = Dts.Variables["ConnectionStringToTest"].Value;

                try                {
                    using (OleDbConnection connection = new OleDbConnection()) {
                        connection.ConnectionString = connString;
                        connection.Open();
                    }
                    Dts.Variables["User::DatabaseCanConnect"].Value = true;                
                }
                catch (Exception ex) {
                    Dts.Variables["User::DatabaseCanConnect"].Value = false;
                }

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

1. 在包范围内创建一个名为 DatabaseCanConnect 的变量.将其设置为布尔值，默认为False.
2. 创建脚本任务.编辑 ReadWriteVariables 属性，然后添加新变量.
3. 将用于构建ForEach循环之外的连接字符串的任何变量添加到您的 ReadOnlyVariables 中.我已将其命名为 ConnectionStringToTest .
4. 将上面的脚本代码添加为Main()函数.请注意，这使用 OleDbConnection .这应该模仿您正在数据流中修改的连接管理器所使用的任何连接.因此，如果是SQL连接，请改用 SqlConnection .
5. 在您的代码中，使用DatabaseCanConnect变量从此处确定流量.您可以在优先约束中使用它，以防止流向数据流，或在条件拆分中使用它(我的偏爱，其他开发者更清楚)，以记录失败时的连接错误，否则按常规进行.

1. Create a variable called DatabaseCanConnect at the package scope. Set it to Boolean, it'll default to False.
2. Create a Script Task. Edit the ReadWriteVariables property and add your new variable.
3. Add to your ReadOnlyVariables whatever variable you're using to build the connection string out of your ForEach loop. I've named mine ConnectionStringToTest.
4. Add the script code above as your Main() function. Note that this using an OleDbConnection. This should mimic whatever connection you're using for the connection manager you're modifying in your data flow. So if it's a SQL connection, use a SqlConnection instead.
5. In your code, use the DatabaseCanConnect variable to determine flow from here. You can use it in a Precedence Constraint to prevent flow to the Data Flow, or use it in a Conditional Split (my preference, more visible to other developers) to log connection errors on failure and proceed as normal otherwise.

这篇关于SSIS遍历服务器并处理失败的登录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！