Execute Package Task in SSIS 2012 has ability to use Project Deployment Model. With selecting Project Reference type as ReferenceType we can select any of packages But just from current project.
This post shows a way to run package from another project in SSIS 2012.
There are some ways to run a package in SSIS 2012, this solution is based on executing package with SSIS 2012 Catalog stored procedures.
There are three stored procedure which commonly in use when you want to run a package;
[catalog].[create_execution]
This stored procedure create an instance of package for execution. Input parameters for this procedure are : Project_name, folder_name, package_name, use32bitruntime. Output parameter is Execution_Id which is a unique identifier.
for example this script will create an instance of package for execution:
Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution]
@package_name=N’P1_Master.dtsx’,
@execution_id=@execution_id OUTPUT,
@folder_name=N’SqlSaturday136′,
@project_name=N’R01_Parameters’,
@use32bitruntime=False,
@reference_id=2
second stored procedure which is commonly in use for package execution is;
[catalog].[set_execution_parameter_value]
This stored procedure will add parameters with values to an instance of package execution. Input parameters are: execution_id (which can be achieved by create_execution procedure), Object_type (type of parameters; 20 for project parameter and 30 for package parameter), parameter_name and Parameter_value.
for example for adding logging level to package execution you can use this script:
DECLARE @var0 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
@execution_id,
@object_type=50,
@parameter_name=N’LOGGING_LEVEL’,
@parameter_value=@var0
third commonly in use procedure for package execution is;
[catalog].[start_execution]
This procedure will execute the instance of package and just need execution_id as input parameter.
So for running a package you can run this script:
Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution]
@package_name=N’P1_Master.dtsx’,
@execution_id=@execution_id OUTPUT,
@folder_name=N’SqlSaturday136′,
@project_name=N’R01_Parameters’,
@use32bitruntime=False,
@reference_id=2
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
GO
Now you just need to add an Execute SQL Task in your package and connect it to SSIS DB database
Then just write down this script, change values related to package name, folder name and other variables.
This will run package from another package.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.