CODEWITHSUNDEEP
ssisazure-data-factory
Ling
Ling

Reputation: 11

Execute SSIS package using storted procedure in ADF v2

I would like to execute a SSIS package in ADFv2.

I created a pipeline and used a stored procedure for it as described here

https://learn.microsoft.com/en-us/azure/data-factory/how-to-invoke-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

But this only works well when database credentials is saved in package. Is there a way to trigger the package with database credentials I configured in Environments properties from Integration Service Catalog?? Thanks in advance for any help!! 

Upvotes: 1

Views: 312

Answers (1)

billinkc
billinkc

Reputation: 61201

In a non-ADF scenario, the create_execution call accepts a parameter of @reference_id

You can divine that value by querying the 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'

Putting it all together, your script could look like 

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

Untested for ADF but it ought to work. Let me know otherwise and I'll see if I can scare up some documentation about the scale out scenarios

Upvotes: 0

Related Questions