在ssis中参数化连接管理器的目的是什么? [英] What is the purpose of parameterizing connection manager in ssis?

问题描述

我有 2 个连接管理器的 ssis 包.

I have ssis package with 2 connection managers.

当部署到 sql server 时，当我右键单击并单击执行时，它允许我设置连接管理器配置值.

When deployed to sql server and when I right click and click execute it allows me to set the connection manager configuration value.

同样在上面的弹出窗口中我可以设置参数值.

Also in the above popup I can set parameter value.

同样，我可以右键单击并选择配置来设置参数和连接管理器值.

Similarly I can right click and choose configure to set the parameter and connection manager values.

那么当我无论如何都可以通过弹出窗口配置连接管理器时，在 ssis 中参数化连接管理器的目的究竟是什么?

So what exactly is the purpose of parameterizing connection managers in ssis when I can anyways configure the connection manager via the pop-up?

推荐答案

参数是包在运行时可以接收的只读变量.包级别参数的示例类似于处理日期.这样我就可以运行昨天的工作，然后用今天的日期重新运行包.

A Parameter is a read only Variable that a package can receive at run time. An example of a package level parameter would be something like Processing Date. That way I can run yesterday's work and then rerun the package with Today's date.

变量也可以在运行时设置，但这样做的机制不太直观.最终结果是一样的.

A Variable can also be set at run-time but the mechanics of doing so are less intuitive. Net result is the same.

项目参数是一个只读变量，项目中的所有包都可以引用.项目级连接管理器的一个示例是文件路径.至少在我的世界中，我将其定义为 C:\ssisdata\MyProject 之类的路径，然后我将 Input/Output/Archive 文件夹挂在该路径上.当我进入生产或其他开发人员的机器时，该值可能会变为 D:\data 或 \server2\share\MyProject

A Project Parameter is a read only Variable that all the packages in a project can reference. An example of a project level connection manager would be a file path. At least in my world, I define that as a path like C:\ssisdata\MyProject and then I have Input/Output/Archive folders hanging off that path. When I get to production, or another developer's machine, maybe that value becomes D:\data or \server2\share\MyProject

如果每个包都定义了 FilePath 的参数，那么我将不得不在运行时修改每个包的参数以反映服务器环境的值.如果我更改了项目中的值，所有包都会采用该新值.

If each package had defined a Parameter of FilePath, then I would have to modify each package's parameter when it runs to reflect the server environment's value. If I change the value in the project, all of the packages pick up that new value.

这一切都在 Visual Studio 的执行环境中.

That's all just in the execution environment from Visual Studio.

当您部署到 SQL Server 的 SSISDB 目录时，您将获得一些不同的选择.

When you deploy to SQL Server's SSISDB catalog, you get some different options.

这里可以设想一个您描述的简单案例.右键单击包并选择执行.FilePath 的粗体文本表示我已针对包的本次 运行更改了它.左侧的图标显示它是项目级别参数(前两个)还是包级别(最后一个).

A simple case as you describe can be envisioned here. Right click on the package and select Execute. The bold text for FilePath indicates I have changed it for this run of the package. The icon to the left show whether it is a project level parameter (first two) or package level (final one).

在幕后，这会生成以下 SQL

Behind the scenes, this generates the following SQL

DECLARE @execution_id bigint;

EXEC SSISDB.catalog.create_execution
    @package_name = N'Package.dtsx'
,   @execution_id = @execution_id OUTPUT
,   @folder_name = N'So'
,   @project_name = N'SO_66497856'
,   @use32bitruntime = False
,   @reference_id = NULL
,   @runinscaleout = False;

SELECT
    @execution_id;

DECLARE @var0 sql_variant = N'D:\ssisdata\MyProject';

EXEC SSISDB.catalog.set_execution_parameter_value
    @execution_id
,   @object_type = 20
,   @parameter_name = N'FilePath'
,   @parameter_value = @var0;

DECLARE @var1 smallint = 1;

EXEC SSISDB.catalog.set_execution_parameter_value
    @execution_id
,   @object_type = 50
,   @parameter_name = N'LOGGING_LEVEL'
,   @parameter_value = @var1;

EXEC SSISDB.catalog.start_execution
    @execution_id;
GO

每次我想运行此作业并使其适用于环境(D: 而不是 C:)时，我都必须单击省略号，...，并提供一个值.

Every time I want to run this job and make it work for the environment (D: instead of C:), I would have to click the ellipses, ..., and provide a value.

有人会把它搞砸，所以你要么像我一样编写 TSQL 脚本并将其放入作业定义中.但是如果我运行 Package2，我需要做相同的运行时级别更改，set_execution_parameter_value 以确保包也使用 D 驱动器.当我到达 Package100 时，我会说一定有更好的方法.

Someone is going to mess that up so either you script the TSQL as I did and put that into the job definition. But if I run Package2, I would need to do the same run-time level change, set_execution_parameter_value to ensure that package also used the D drive. By the time I get to Package100, I'd say there must be a better way.

如果我右键单击我的项目 SO_66497865，我可以选择Configure...

If I right click on my Project, SO_66497865, I have an option for Configure...

您可以看到我将值更改为 D 驱动器上完全不同的路径.SQL 在幕后使用 set_object_parameter_value

You can see me changing the value to an entirely different path on the D drive. Behind the scenes SQL is working with set_object_parameter_value

DECLARE @var sql_variant = N'D:\Set\Configure\Value';

EXEC SSISDB.catalog.set_object_parameter_value
    @object_type = 20
,   @parameter_name = N'FilePath'
,   @object_name = N'SO_66497856'
,   @folder_name = N'So'
,   @project_name = N'SO_66497856'
,   @value_type = V
,   @parameter_value = @var;
GO

现在当我去运行同一个包时，看看那个

Now when I go to run the same package, look at that

它使用已配置的项目参数值，而我不必提供每次运行覆盖(无粗体文本).

It uses the Configured project parameter value without me having to provide a per run override (no bolded text).

为了完整性，您可以做的最后一件事是创建一个环境".环境是一组共享变量值.例如，我的 Oracle 用户名和密码(标记为敏感)可能是环境级别的东西，因为我的 4 个项目中的任何一个都可能希望将该值用于配置目的.环境 SOEnvironment 可用于任何项目.

For completeness, the last thing you can do is create an "Environment". An Environment is a set of shared variable values. For example, my Oracle User name and password (marked as sensitive) could be an Environment level thing because any of my 4 projects might want to use that value for configuration purposes. The Environment SOEnvironment is available to any of the projects.

我要将 MagicNumber 从我的环境连接到我的项目的 OtherProjectParameter.

I'm going to wire up MagicNumber from my Environment to my project's OtherProjectParameter.

再次，右键单击一个项目并选择配置.转到引用"选项卡(这是一次性活动)并单击添加"，然后找到环境".

Once again, right click on a project and choose Configure. Go to the References tab (this is a one time activity) and click Add and then find the Environment.

现在，返回到参数选项卡并单击其他项目参数上的省略号.请注意，使用环境变量现在不再是灰色的.这会根据数据类型显示允许的环境变量.选择魔数

Now, back to Parameters tab and click the ellipses on OtherProjectParameters. Notice that Use environment variable is now longer greyed out. This shows you allowable environment variables based on data type. Pick MagicNumber

当您单击确定时，您现在在配置屏幕上有一个下划线

When you click OK, you now have an underscore on the configure screen

此时，当我去运行包时，它会显示这样的内容

At this point, when I go to run the package, it will show me something like this

选择您的环境，然后填写OtherProjectParameter

Pick your environment and that will get the OtherProjectParameter to fill in

这是一个关于您的选择是什么以及它们在什么时候/何时重要的旋风之旅.您应该如何配置事物很大程度上取决于您的参数化需求.

That's a whirlwind tour of what your choices are and what/when they matter. How you should configure things is extremely dependent on your parameterization needs.

如果您启用了多个配置，那么当您去执行包时 - 无论是作为一次性执行还是 SQL 代理作业，您都必须选择环境.在这里，我有 SOEnvironment、SO_67402693_env0 和 SO_67402693_env1 作为我的包和您删除的问题的来源，后两种环境都为参数 p 提供了一个值，该参数用于配置 OtherProjectParameter

If you have multiple configurations enabled, then when you go to execute the package - either as a one-off execution or a SQL Agent job, you must pick the environment. Here I have SOEnvironment, SO_67402693_env0, and SO_67402693_env1 as sources for my package and for your deleted question, the latter two environments both provide a value for parameter p which is configuring OtherProjectParameter

当我去执行包时，它会标记它在选择环境之前无法启动.在这里，我选择 env0 并生成以下 tsql.@reference_id = 20002 是如何确定优先级的，事实上，没有优先级，因为在运行时只允许一个环境引用.

When I go to execute the package, it will flag that it cannot start until an environment is picked. Here I select env0 and it results in the following tsql being generated. The @reference_id = 20002 is how that precedence would be determined an in fact, there is no precedence as only one environment reference is allowed at runtime.

DECLARE @execution_id bigint;

EXEC SSISDB.catalog.create_execution
    @package_name = N'Package.dtsx'
,   @execution_id = @execution_id OUTPUT
,   @folder_name = N'So'
,   @project_name = N'SO_66497856'
,   @use32bitruntime = False
,   @reference_id = 20002
,   @runinscaleout = False;

SELECT
    @execution_id;

DECLARE @var0 smallint = 1;

EXEC SSISDB.catalog.set_execution_parameter_value
    @execution_id
,   @object_type = 50
,   @parameter_name = N'LOGGING_LEVEL'
,   @parameter_value = @var0;

EXEC SSISDB.catalog.start_execution
    @execution_id;
GO

如果这是通过 SQL 代理完成的，而不是右键单击要执行的程序包，则会生成类似的命令，但允许的相同单个环境引用将成立.

Similar commands are generated if this is done via SQL Agent instead of right clicking on a package to execute but the same single environment reference allowed will hold true.

向后兼容性.2005/2008 的模式是让 SSIS 连接字符串具有由变量驱动的表达式，然后由经典配置驱动，或者只使用配置将值直接注入到 ConnnectionString 属性.有些人继续使用这种方法，就像项目部署模型一样.其他人使用包/项目管理器传递凭据或连接字符串.我更喜欢使用弹出窗口来处理连接管理器的配置，因为这是一个不太容易处理的部分.

Backwards compatibility. The pattern for 2005/2008 was to have SSIS connection strings with expressions driven by variables which were then driven by classic Configuration or to just use Configuration to directly inject values to the ConnnectionString attributes. Some people continue to use that approach as is with the Project Deployment Model. Others use Package/Project managers to pass in credentials or a connection string. I favor using the pop-up window to handle configuring connection managers as it's one less moving part to deal with.

项目/包参数的一个参数是 ftp 凭据.我上次使用的现有 FTP 任务将失败，如果预期文件不存在.我的模式是编写一个 .NET 脚本来处理 FTP 活动，因为我可以更好地处理丢失文件的情况.但是，我需要将凭证数据安全地传递给我的包，因此，我需要包参数，我会选中敏感框.如果我在运行时提供它们，那么它们将在 SQL 代理作业步骤中以明文形式保存.

An argument for project/package parameters is ftp credentials. The existing FTP task, last I used it, would fail if the expected file wasn't there. My pattern was to write a .NET script to handle FTP activities as I could better handle missing file scenarios. But, I would need to get credential data passed securely to my package and thus, I needed package parameters and I would check the Sensitive box. Were I to have supplied them at run-time, then they would be saved in clear text in the SQL Agent job steps.

这篇关于在ssis中参数化连接管理器的目的是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！