Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

SSIS 2012 T-SQL Package Execution with Dynamic Environment Parameter

  • 16 March 2014
  • Author: TyroneBrown
  • Number of views: 9723

SQL Server 2012 provides the useful feature of SSIS package execution via T-SQL commands. One issue, I’ve encountered is the need to retrieve a deployed SSIS project’s configured environment, and pass the environment’s reference ID into the T-SQL script executing the SSIS package.

The need for this solution arose from the inability to execute SSIS packages deployed using the Project Deployment Model from a different project using an Execute Package Task with a Reference Type of External Reference. This bug means that master packages will need to call packages via T-SQL when they’re deployed in a different project. You can vote for Microsoft to address this bug in the next release cycle here. For now, the below stored procedure will be useful in executing packages across different environments/servers.

For now the below stored procedure will accept your respective SSIS project name, SSIS Catalog folder name, and package name and execute your package with the environment configured on the server.


@ProjectName nvarchar(128)
, @FolderName nvarchar(100)
, @PackageName nvarchar(260)

DECLARE @Reference_id bigint
, @execution_id bigint

SET @reference_id =
SELECT e.reference_id
FROM SSISDB.catalog.environment_references e
INNER JOIN SSISDB.catalog.projects p
ON e.project_id = p.project_id
WHERE = @ProjectName
EXEC [SSISDB].[catalog].[create_execution] @package_name=@PackageName, @execution_id=@execution_id
OUTPUT, @folder_name=@FolderName, @project_name=@ProjectName, @use32bitruntime=False, @reference_id=@reference_id
SELECT @execution_id
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

Categories: SQL Server
Rate this article:
No rating


Other posts by TyroneBrown

Please login or register to post comments.