SSIS Catalog: How to use environment in every type of package execution
- by Kevin Shyr
Here is a good blog on how to create a SSIS Catalog and setting up environments. http://sqlblog.com/blogs/jamie_thomson/archive/2010/11/13/ssis-server-catalogs-environments-environment-variables-in-ssis-in-denali.aspx
Here I will summarize 3 ways I know so far to execute a package while using variables set up in SSIS Catalog environment.
First way, we have SSIS project having reference to environment, and having one of the project parameter using a value set up in the environment called "Development". With this set up, you are limited to calling the packages by right-clicking on the packages in the SSIS catalog list and select Execute, but you are free to choose absolute or relative path of the environment.
The following screenshot shows the 2 available paths to your SSIS environments. Personally, I use absolute path because of Option 3, just to keep everything simple for myself.
The second option is to call through SQL Job. This does require you to configure your project to already reference an environment and use its variable. When a job step is set up, the configuration part will require you to select that reference again. This is more useful when you want to automate the same package that needs to be run in different environments.
The third option is the most important to me as I have a SSIS framework that calls hundreds of packages. The main part of the stored procedure is in this post (http://geekswithblogs.net/LifeLongTechie/archive/2012/11/14/time-to-stop-using-ldquoexecute-package-taskrdquondash-a-way-to.aspx). But the top part had to be modified to include the logic to use environment reference.
CREATE PROCEDURE [AUDIT].[LaunchPackageExecutionInSSISCatalog]
@PackageName NVARCHAR(255)
, @ProjectFolder NVARCHAR(255)
, @ProjectName NVARCHAR(255)
, @AuditKey INT
, @DisableNotification BIT
, @PackageExecutionLogID INT
, @EnvironmentName NVARCHAR(128) = NULL
, @Use32BitRunTime BIT = FALSE
AS
BEGIN TRY
DECLARE @execution_id BIGINT = 0;
-- Create a package execution
IF @EnvironmentName IS NULL BEGIN
EXEC [SSISDB].[catalog].[create_execution]
@package_name=@PackageName,
@execution_id=@execution_id OUTPUT,
@folder_name=@ProjectFolder,
@project_name=@ProjectName,
@use32bitruntime=@Use32BitRunTime;
END
ELSE BEGIN
DECLARE @EnvironmentID AS INT
SELECT @EnvironmentID = [reference_id]
FROM SSISDB.[internal].[environment_references] WITH(NOLOCK)
WHERE [environment_name] = @EnvironmentName
AND [environment_folder_name] = @ProjectFolder
EXEC [SSISDB].[catalog].[create_execution]
@package_name=@PackageName,
@execution_id=@execution_id OUTPUT,
@folder_name=@ProjectFolder,
@project_name=@ProjectName,
@reference_id=@EnvironmentID,
@use32bitruntime=@Use32BitRunTime;
END
