使用ADF v2中的步骤执行SSIS软件包

Question

我想在ADFV2中执行SSIS软件包。

我创建了一个管道并使用了一个存储过程，如下所述

https://learn.microsoft.com/en-us/azure/data-factory/how-to-to-invoke-ssis-ssis-package-stored-procedure-activity。

DECLARE @return_value INT, @exe_id BIGINT, @err_msg NVARCHAR(150)    
EXEC @return_value=[SSISDB].[catalog].[create_execution] 
@folder_name=N'xxx', 
@project_name=N'xxx', 
@package_name=N'xxx.dtsx', 
@use32bitruntime=0, @runinscaleout=1, @useanyworker=1, @execution_id=@exe_id OUTPUT    
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @exe_id, @object_type=50, @parameter_name=N'SYNCHRONIZED', @parameter_value=1    
EXEC [SSISDB].[catalog].[start_execution] @execution_id=@exe_id, @retry_count=0    
IF(SELECT [status] FROM [SSISDB].[catalog].[executions] WHERE execution_id=@exe_id)<>7 
BEGIN 
SET @err_msg=N'Your package execution did not succeed for execution ID: ' 
+ CAST(@exe_id AS NVARCHAR(20)) RAISERROR(@err_msg,15,1) 
END

但是，只有当数据库凭据保存在软件包中时，这才能很好地工作。是否有一种方法可以用我在Integration Service目录中配置在环境属性中的数据库凭据触发软件包？事先感谢您的任何帮助！！

Answer 1

在非ADF方案中，create_execution调用接受@reference_id

的参数

您可以通过查询SSISDB

来使该值神圣

SELECT ER.reference_id
FROM SSISDB.catalog.folders AS F
INNER JOIN SSISDB.catalog.environments AS E
ON E.folder_id = F.folder_id
INNER JOIN SSISDB.catalog.projects AS P
ON P.folder_id = F.folder_id
INNER JOIN SSISDB.catalog.environment_references AS ER
ON ER.project_id = P.project_id
WHERE F.name = 'MyFolder' AND E.name = 'EnvDemo' AND P.name = 'MyIspac'

将所有内容放在一起，您的脚本看起来像

DECLARE @return_value INT, @exe_id BIGINT, @err_msg NVARCHAR(150), @refid bigint;
SELECT @refid = ER.reference_id
FROM SSISDB.catalog.folders AS F
INNER JOIN SSISDB.catalog.environments AS E
ON E.folder_id = F.folder_id
INNER JOIN SSISDB.catalog.projects AS P
ON P.folder_id = F.folder_id
INNER JOIN SSISDB.catalog.environment_references AS ER
ON ER.project_id = P.project_id
WHERE F.name = 'MyFolder' AND E.name = 'EnvDemo' AND P.name = 'MyIspac';
EXEC @return_value=[SSISDB].[catalog].[create_execution] 
@folder_name=N'xxx', 
@project_name=N'xxx', 
@package_name=N'xxx.dtsx', 
@reference_id = @refid
@use32bitruntime=0, @runinscaleout=1, @useanyworker=1, @execution_id=@exe_id OUTPUT

未经测试的ADF，但应该工作。让我知道否则，我会看看我是否可以吓tocar