在 SSIS 托管对象模型中使用参数覆盖 [英] using Parameter overrides in SSIS Managed Object Model

问题描述

我使用托管对象模型来执行我的 SSIS 2012 包.

I am using the managed Object Model to execute my SSIS 2012 packages.

我正在尝试使用 PackageInfo.PropertyOverrideParameterSet 来覆盖在 SSIS 2012 中自动公开的连接字符串参数.

I am trying to use PackageInfo.PropertyOverrideParameterSet to override connection string parameters that are automatically exposed in SSIS 2012.

SSIS PackageInfo 类提供了重载的Execute 方法 允许您传入要在运行时覆盖的参数.这似乎是设置连接字符串的绝佳机会.

SSIS PackageInfo class provides an overloaded Execute method that allows you to pass in Parameters you want overridden at run-time. THis seems like a perfect opportunity to set connection strings.

创建PackageInfo.PropertyOverrideParameterSet object 但是您需要指定要覆盖的参数的 PropertyPath.

To create a PackageInfo.PropertyOverrideParameterSet object however you need to specify the PropertyPath of the parameter you intend to override.

需要采用什么格式?

推荐答案

可以使用 PackageInfo.ExecutionValueParameterSet 而不是 PackageInfo.PropertyOverrideParameterSet.ParameterName 用于连接字符串将遵循 CM..ConnectionString 格式(将 替换为您的连接管理器名称).在下面的代码段中，我的连接管理器名为 B540P.KB1).

Setting a connection string can be done using PackageInfo.ExecutionValueParameterSet instead of the PackageInfo.PropertyOverrideParameterSet. The ParameterName for a connection string would follow the format CM.<connectionManagerName>.ConnectionString (replace <connectionManagerName> with your connection manager name). In the snippet below, my connection manager is named B540P.KB1).

PackageInfo.ExecutionValueParameterSet evps = new PackageInfo.ExecutionValueParameterSet();
evps.ParameterName = @"CM.B540P.KB1.ConnectionString";
evps.ParameterValue = @"Data Source=B540P;Initial Catalog=AdventureWorks;Integrated Security=True;Application Name=SSIS-Package1-{19035BA0-C90C-47AA-8AF6-31B025779FF6}B540P.KB1;";
evps.ObjectType = 30;

System.Collections.ObjectModel.Collection<PackageInfo.ExecutionValueParameterSet> parms = new System.Collections.ObjectModel.Collection<PackageInfo.ExecutionValueParameterSet>();
parms.Add(evps);           

pkg.Execute(false, null, parms, null);

可以使用 PackageInfo.PropertyOverrideParameterSet 来设置连接字符串，但我不确定 PropertyPath 将是(有关 PropertyPath 用于 SSIS 变量).

It may be possible to use PackageInfo.PropertyOverrideParameterSet to set the connection string, but I'm not sure what the PropertyPath would be (see SQL Server link below for an example of a PropertyPath for an SSIS variable).

这里有一些关于通过不同方法与 MOM 交互的好信息:

Here's some good information on interacting with MOM via different approaches:

• C# - SSIS 目录管理对象模型概览 - MSDN博客
• SQL Server - Microsoft SQL Server 2012 集成服务
  • 查看属性覆盖部分以查看设置 SSIS 变量值的示例(它显示了 PropertyPath)
  • C# - A Glimpse of the SSIS Catalog Managed Object Model - MSDN Blogs
  • SQL Server - Microsoft SQL Server 2012 Integration Services
    • Check out the Property Overrides section to see an example of setting the value of an SSIS Variable (it shows an example of a PropertyPath)

    这篇关于在 SSIS 托管对象模型中使用参数覆盖的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！