自动化SSIS项目部署，创建环境，环境变量，环境参考和将项目参数映射到环境变量

问题描述：

我正在尝试自动执行从部署ssis项目到引用环境以及使用环境变量映射项目参数的所有步骤

I am trying to automate all steps from deploying a ssis project to referencing an environment and mapping Project Parameters with environment varaibles

1）我已将ispac文件部署到服务器

1) I have deployed the ispac file to server

2）在集成目录的SSISDB下创建了一个环境

2) Created an environment under SSISDB of Integration catalog

3）创建的环境变量在上面创建的环境中。

3) Created environment variables on the above created environment.

4）创建环境参考

4) Created an environment reference

5）将项目参数映射到环境变量。

5) Mapped the Project Parameters to environment variables.

所有这些都可以通过脚本完成，而不是通过SSMS完成吗？

Can all this be done by a script instead of doing from SSMS ?

答

您好，

Hello,

是的，所有这些步骤都可以使用T-SQL脚本完成并在Agent中安排工作。

Yes, all these steps can be done using T-SQL scripts and scheduled in Agent Job.

对于SSIS项目部署，请参阅部署S来自SSMS的带有Transact-SQL的SIS项目

For SSIS project deployment, please refer to Deploy an SSIS project from SSMS with Transact-SQL

DECLARE @ProjectBinary AS varbinary(max)
DECLARE @operation_id AS bigint
SET @ProjectBinary =
    (SELECT * FROM OPENROWSET(BULK '<project_file_path>.ispac', SINGLE_BLOB) AS BinaryData)

EXEC catalog.deploy_project @folder_name = '<target_folder>',
    @project_name = '<project_name',
    @Project_Stream = @ProjectBinary,
    @operation_id = @operation_id out

脚本SSMS可以自动生成步骤2到5，点击"新查询编辑器窗口"。在"脚本"中选项。

The scripts for step 2 to 5 can be automatically generated by SSMS, clicking "New Query Editor Window" in "Script" option.

创建环境示例：

Example for create environment:

DECLARE @var int = N'1'
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'TestVar', @sensitive=False, @description=N'', @environment_name=N'TestEnv', @folder_name=N'TestFolder', @value=@var, @data_type=N'Int32'
GO

自动化SSIS项目部署，创建环境，环境变量，环境参考和将项目参数映射到环境变量

相关推荐