SSIS Master package executing packages from another project

Question

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

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 ?

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

Answer 1

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

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. 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

An example of the 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 

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.