如何在 SSIS 2012 中自动设置 SSIS 项目参数? [英] How can I automate setting SSIS Project Parameters in SSIS 2012?

问题描述

我找到了显示参数的 catalog.object_parameters 视图，但没有创建我宁愿避免的环境，我想找到一种方法来复制项目级别的配置"对话框与存储过程调用.我似乎找不到符合要求的目录"存储过程.

I found the catalog.object_parameters view that displays the parameters, but short of creating Environments, which I'd rather avoid, I'd like to find a way to replicate the "Configure" dialog box at the Project level with Stored Procedure calls. I can't seem to find a "catalog" stored procedure that fits the bill.

有人破解这个坚果吗?如何在部署(而非执行)时设置项目级参数?

Has anyone cracked this nut? How do I set a Project-level Parameter at deployment (not execution) time?

推荐答案

我一直在寻找一种更快的方法来自己动手

I was seeking a quicker way to do it my self

简而言之，我在集成服务目录上部署的项目的配置菜单中手动更改了所有连接管理器和项目参数，在保存之前我单击了脚本到按钮，它创建了一系列插入更新脚本这看起来像是在更新旧的 ssis_configs 表.然后我编写了一个重新配置参数值的 power shell 脚本，即为每个环境等配置参数值.在我的情况下，我的开发项目正在查看某个模式，当在 prod 中时它看起来在别处，在 dev 中我限制了可执行文件的数量，但在 prod我让他们拥有它.这很容易在 powershell 中设置为变量，然后在设置配置中创建更改 SQL 脚本 (SqlCMD) 脚本也可以很好地工作

In brief I went and manual made changes to all the connection managers and project parameters in the Configure menu of the project deployed on the Integrations Services Catalog, before saving I clicked the Script to button and it created a series of insert update scripts that just look like its updating the old ssis_configs table. I then wrote a power shell script that re configure the parameter values i.e. for each environment etc. in my case my dev projects were looking at a certain schema and when in prod it looks elsewhere and in dev I limit the number of executables but in prod I let em have it. this is easily set as variables in the powershell that then creates the change in the set configurations SQL script (SqlCMD) script will work nicely too

简单地说，您可以使用名为

simplistically you can use the stored proc called

[SSIS].[catalog].[set_object_parameter_value]

你需要知道参数

@object_type = 20,
@parameter_name = N'your parameter by name',
@object_name = N'the ssis project name for a project param package for other',
@folder_name = N'the folder the project is in SSISDB',
@project_name = N'project name again'
@value_type = V -- not sure what this is for,
@parameter_value = N'YOUR NEW VALUE!!'

这篇关于如何在 SSIS 2012 中自动设置 SSIS 项目参数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！