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.
CREATE PROCEDURE usp_ExecuteSSIS
, @FolderName nvarchar(100)
, @PackageName nvarchar(260)
DECLARE @Reference_id bigint
, @execution_id bigint
SET @reference_id =
FROM SSISDB.catalog.environment_references e
INNER JOIN SSISDB.catalog.projects p
ON e.project_id = p.project_id
WHERE p.name = @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
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