SSIS 2012: Execute Package from Another Project

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

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. 

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

Leave a Reply

Your email address will not be published. Required fields are marked *