SSIS 2012 T-SQL Package Execution with Dynamic Environment Parameter

SSIS 2012 T-SQL Package Execution with Dynamic Environment Parameter

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
@ProjectName nvarchar(128)
, @FolderName nvarchar(100)
, @PackageName nvarchar(260)

AS
BEGIN
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 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
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
END
GO

 
Comments

In the [SSISDB].[set_execution_parameter_value], can I work with variables in the ‘Environments’ folder of the SSIS catalog to specify which to run or does it only use the last configured variable?