将 SSIS 包配置应用于多个包 [英] Applying SSIS Package Configuration to multiple packages

问题描述

我有大约 85 个使用相同连接管理器的 SSIS 包.我知道每个包都有自己的连接管理器.我试图决定什么是最好的配置方法，可以根据包所在的服务器简单地设置连接管理器的连接字符串.我在网上访问了各种建议，但找不到任何地方可以简单地将配置从一个包复制到其余包的做法.显然有很多方法，例如XML文件、SQL Server、环境变量等.

I have about 85 SSIS packages that are using the same connection manager. I understand that each package has its own connection manager. I am trying to decide what would be the best configurations approach to simply set the connectionstring of the connection manager based on the server the packages are residing on. I have visited all kinds of suggestions online, but cannot find anywhere the practice where I can simply copy the configuration from one package to the rest of the packages. There are obviously many approaches such as XML file, SQL Server, Environment Variable, etc.

所有文章都指出通过使用 XML 或 SQL 方法来使用间接方法.为什么使用环境变量来保存连接字符串是一种糟糕的方法?

All the articles out there are pointing to use an Indirect method by using XML or SQL approach. Why would using an environment variable for just holding a connection string is such a bad approach?

非常感谢任何建议.

谢谢！

推荐答案

为什么使用环境变量来保存连接字符串是一种糟糕的方法?

我发现环境变量或注册表项配置方法受到一次只能配置一项的严重限制.对于连接字符串，您需要为给定服务器上的每个目录定义一个环境变量.也许只有 2 或 3 个，而且这是可以管理的.我们有一个很好的每个数据库实例 30 多个，并且我们有多实例机器，所以你可以看到这个问题以多快的速度发展为维护噩梦.相比之下，基于表或 xml 的方法可以为给定的配置键保存多个配置项.

Why would using an environment variable for just holding a connection string is such a bad approach?

I find the environment variable or registry key configuration approach to be severely limited by the fact that it can only configure one item at a time. For a connection string, you'd need to define an environment variable for each catalog on a given server. Maybe it's only 2 or 3 and that's manageable. We had a good 30+ per database instance and we had multi-instanced machines so you can see how quickly this problem explodes into a maintenance nightmare. Contrast that with a table or xml based approach which can hold multiple configuration items for a given configuration key.

如果你走这条路，我建议创建一个变量 ConnectionString 并使用它来配置属性.这是一个额外的步骤，但我再次发现在变量上调试复杂表达式比在属性上调试复杂表达式更容易.使用变量，您可以随时在包上弹出断点并查看本地窗口以查看当前值.

If you go this route, I'd propose creating a variable, ConnectionString and using it to configure the property. It's an extra step but again I find it's easier to debug a complex expression on a variable versus a complex expression on a property. With a variable, you can always pop a breakpoint on the package and look at the locals window to see the current value.

创建一个名为 ConnectionString 的变量后，我右键单击它，选择 Properties 并将 EvaluateAsExpression 设置为 True，并将 Expression 属性设置为类似 "Data Source="+ @[System::MachineName] +"\\DEV2012;Initial Catalog=FOO;Provider=SQLNCLI11.1;Integrated Security=SSPI;"

After creating a variable named ConnectionString, I right click on it, select Properties and set EvaluateAsExpression equal to True and the Expression property to something like "Data Source="+ @[System::MachineName] +"\\DEV2012;Initial Catalog=FOO;Provider=SQLNCLI11.1;Integrated Security=SSPI;"

当它被评估时，它会填写当前机器的名称 (DEVSQLA)，我会有一个有效的 OLE DB 连接字符串，它连接到一个命名实例 DEV2012.

When that is evaluated, it'd fill in the current machine's name (DEVSQLA) and I'd have a valid OLE DB connection string that connects to a named instance DEV2012.

Data Source=DEVSQLA\DEV2012;Initial Catalog=FOO;Provider=SQLNCLI11.1;Integrated Security=SSPI;

如果您有比一个变量更复杂的配置需求，那么我可以看到您使用它来配置一个连接管理器到一个 sql 表，该表包含所有配置键和值的完整存储库.

If you have more complex configuration needs than just the one variable, then I could see you using this to configure a connection manager to a sql table that holds the full repository of all the configuration keys and values.

我将通过程序化路线修改所有 80something 包.我们从第三方收到了一堆包，他们没有按照我们的程序进行配置和记录.代码并不是非常难，如果您准确描述了为解决您的需求而进行的更改类型，我很乐意在此答案上添加一些代码.它可以像下面一样简单.调用该函数后，它将通过将 SSISDB ole 连接管理器上的 sql server 配置添加到名为 Default.2008.Sales 的过滤器的名为 dbo.sysdtsconfig 的表中来修改包.

I'd go about modifying all 80something packages through a programmatic route. We received a passel of packages from a third party and they had not followed our procedures for configuration and logging. The code wasn't terribly hard and if you describe exactly the types of changes you'd make to solve your need, I'd be happy to toss some code onto this answer. It could be as simple as the following. After calling the function, it will modify a package by adding a sql server configuration on the SSISDB ole connection manager to a table called dbo.sysdtsconfig for a filter named Default.2008.Sales.

string currentPackage = @"C:\Src\Package1.dtsx"

public static void CleanUpPackages(string currentPackage)
{
    p = new Package();
    p.app.LoadPackage(currentPackage, null);
    Configuration c = null;

    // Apply configuration Default.2008.Sales
    // ConfigurationString => "SSISDB";"[dbo].[sysdtsconfig]";"Default.2008.Sales"
    // Name => MyConfiguration
    c = p.Configurations.Add();
    c.Name = "SalesConfiguration";
    c.ConfigurationType = DTSConfigurationType.SqlServer;
    c.ConfigurationString = @"""SSISDB"";""[dbo].[sysdtsconfig]"";""Default.2008.Sales""";

    app.SaveToXml(sourcePackage, p, null);
}

将变量添加到包中不会花费更多的代码.在清理过程中，添加这样的代码，将一个新变量添加到您的包中，该变量具有与上述类似的表达式.

Adding a variable in to the packages would not take much more code. Inside the cleanup proc, add code like this to add a new variable into your package that has an expression like the above.

string variableName = string.Empty;
bool readOnly = false;
string nameSpace = "User";
string variableValue = string.Empty;
string literalExpression = string.Empty;

variableName = "ConnectionString";
literalExpression = @"""Data Source=""+ @[System::MachineName] +""\\DEV2012;Initial Catalog=FOO;Provider=SQLNCLI11.1;Integrated Security=SSPI;""";

p.Variables.Add(variableName, readOnly, nameSpace, variableValue);
p.Variables[variableName].EvaluateAsExpression = true;
p.Variables[variableName].Expression = literalExpression;

如果我遗漏了什么，或者您想澄清任何要点，请告诉我.

Let me know if I missed anything or you'd like clarification on any points.

这篇关于将 SSIS 包配置应用于多个包的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！