SSIS Master程序包执行另一个项目中的程序包 [英] SSIS Master package executing packages from another project

问题描述

我有多个SSIS项目，但是其中的某些软件包是相同的.

I have multiple SSIS projects, but some of the packages inside them are the same.

我想创建一个包含所有 generic 软件包的项目，并保留其他项目及其特定软件包.

I would like to create a project with all the generic packages and keep the others projects with theirs specific packages.

所以我的问题是:是否有一个可以执行并将父变量传递给另一个项目的包的主包?

So my question is : is it possible to have a master package that can execute and pass parent variables to packages from another project ?

我是SSIS的新手，所以很抱歉，如果这是一个明显的问题，或者我不够具体

I'm new to SSIS so sorry if it's an obvious question or if i'm not specific enough

推荐答案

配置Integration Services目录时，可以从另一个项目中执行软件包.

When you configure an Integration Services catalog you can execute the packages from another project.

1. 在SSIS目录中部署所有项目之后.转到SSIS目录并浏览到要执行的程序包.
2. 右键单击并选择执行
3. 它将弹出一个窗口，然后要求填写所需的参数
4. 单击script菜单，然后选择New query editor window
5. 其他步骤-删除Select @execution_id(不必要)
6. 其他步骤-删除DECLARE @var0 smallint = 1行并将@var0替换为1(在该行中将为@ parameter_value = 1)
7. 请勿关闭sql查询窗口，因为您需要复制生成的脚本

1. After deploying your all projects in the SSIS Catalog. Go to SSIS Catalog and browse to the package you want to execute.
2. Right click and select execute
3. It will pop up a window and ask for required paramter to fill up then
4. Click on the script menu and select New query editor window
5. Extra steps - Delete the Select @execution_id (not neccessary)
6. Extra steps - Delete the DECLARE @var0 smallint = 1 line and replace @var0 with 1 (in the line it will be @parameter_value=1 )
7. Do not close the sql query window as you need to copy the generated script

现在在您的主软件包中:

Now in your master package:

1. 添加Execute SQL Task
2. 将OLE DB connection manage r添加到SSISDB数据库
3. 在Execute SQL Task Editor-> SQLstatement中:将生成的程序包脚本粘贴到此处

1. Add a Execute SQL Task
2. Add OLE DB connection manager to SSISDB database
3. In the Execute SQL Task Editor--> SQLstatement: paste here the generated package script

脚本示例:

Declare @execution_id bigint  
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'testpackage.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'TestFolder', @project_name=N'TestProject', @use32bitruntime=False, @reference_id=Null  
Select @execution_id --delete this line  
DECLARE @var0 smallint = 1 `-- delete this line`   
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0 `--(replace this @var0 with 1)`  
EXEC [SSISDB].[catalog].[start_execution] @execution_id  
GO

传递参数:添加以下两行以添加传递参数

Passing parameter: Add the following two line to add pass a paremeter

DECLARE @ReportDate nvarchar(100) = `?`  
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=30, @parameter_name=N'ReportDate', @parameter_value=@ReportDate 

现在，从Execute SQL Task Editor的参数映射"标签中添加您要作为参数传递的变量.

Now from "Parameter Mapping" tab on the Execute SQL Task Editor add your variable you want to pass as a parameter.

请记住，变量数据类型必须与参数数据类型相同.

Remember that variable data type has to be same as the parameter data type.

这篇关于SSIS Master程序包执行另一个项目中的程序包的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！