SSIS 2012: Execute Package from Another Project

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 Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
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.

Leave a Reply