If you decide to upgrade from SSIS 2008 to SSIS 2012 you might decide to use the Project Deployment Model and deploy your SSIS packages to the SSISDB instead of using the Package Deployment Model and deploy your packages to the File System. The Project Deployment Model brings a lot of advantages, but also some issues that you have to solve.
One of these issues is how to call packages that are part of a different SSIS project. For example you have several SSIS projects, e.g. one project for the packages that load the dimensions, one project for fact packages, one project for workflow packages.
To solve this issue you can use an Execute SQL Task, because when you execute packages on the server the execution information is inserted in the SSIS internal tables and then stored procedures are executed that run the SSIS package. To get the relevant code open SSMS, navigate to the folder in the SSIS catalog where your SSIS package is located, right-click on the package name and choose execute.
This generates the following code:
Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Load_DimProduct.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'_TRDemo', @project_name=N'Dimension', @use32bitruntime=False, @reference_id=Null
DECLARE @var0 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0
EXEC [SSISDB].[catalog].[start_execution] @execution_id
If a Connection Manager to the SSISDB is not yet available then create it as a project connection.
Execute the workflow package in the SSISDB and check the results in the execution reports of the workflow package and the packages that you call in the workflow package. You will see that the workflow package started the other packages and that the other packages were executed successfully.
In my next post I’ll explain how to make this solution more flexible. The current solution still has some disadvantages like redundant T-SQL code in every Execute SQL Task and it only works for exactly one environment reference.