SSIS Execute Package Task on SQL Server 2016

2
votes

In Visual Studio I am creating a master package that I will use to execute a number of child packages using the Execute Package Task.

The child packages have been deployed to the Integration Services Catalog SSISDB on SQL Server 2016.

When configuring my Execute Package Task to execute a child package I am not able to access the SSISDB on the server. How can I access the SSISDB?

It seems the task is expecting the child package to be located in the MSDB on the Integration Services Server.

The following are the configurations made I the Execute Package Task

  • Reference Type: External Reference
  • Location: SQL Server
  • Connection: .SSISDB
  • PackageName: ? - Unable to select child package
  • Password: *********
  • ExecuteOutOfProcess: False

Update: Changing to package deployment mode does not change the behavior of the Execute Package Task. I am still not able to access the packages stored in the SSISDB

enter image description here

1
sql-serverssis
Is the child package within the same project as the parent? – billinkc
No, the child package is in a different project. – Peter Larsen

1 Answers

1
votes

Looks like you deployed packages with packages deployment mode. Switch deployment mode to package: Project - Convert to project deployment mode: enter image description here

and redeploy package. To run package from another project you have to place "Execute SQL Task" with SSIS.create_execution stored procedure (https://msdn.microsoft.com/en-us/library/ff878034(v=sql.110).aspx) on control flow instead of "Execute Package Task", use following T-SQL as example (copied from mentioned link):

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Child1.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'TestDeply4', @project_name=N'Integration Services Project1', @use32bitruntime=False, @reference_id=Null
Select @execution_id
DECLARE @var0 sql_variant = N'Child1.dtsx'
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=20, @parameter_name=N'Parameter1', @parameter_value=@var0
DECLARE @var1 sql_variant = N'Child2.dtsx'
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=20, @parameter_name=N'Parameter2', @parameter_value=@var1
DECLARE @var2 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var2
EXEC [SSISDB].[catalog].[start_execution] @execution_id