SSIS Catalog: How to use environment in every type of package execution

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

Print | posted on Thursday, November 22, 2012 6:59 AM

Feedback

# re: SSIS Catalog: How to use environment in every type of package execution

Left by Russell Young at 10/1/2014 3:09 PM
Gravatar In our 2008 packages we have an OS environmental variable that tell the package what server the SQL configuration table is on. When the package run the package configuration go to that table and get variable that every project uses(such as workspace). I am trying to implement this in 2012(SSDT) but all it talks about is environmental variables that are unique to the project. I was wondering if your stored procedure could be used to get those variable that all projects will share. Or do you have a better solution for 2012.

# re: SSIS Catalog: How to use environment in every type of package execution

Left by LifeLongTechie at 10/3/2014 4:38 PM
Gravatar I always keep my variables name the same across all my packages: @Config_MetaConn, which stores my connection string to the server where the rest of the config values live. The first task of the package is to retrieve and override any @Config_??? that need to change. If you are doing it this way, then you can simply use the provided SP to set your package variable in your SP execution.

That being said, the connection you are using to retrieve SQL configuration table must be the same across all of your package, so if your SSIS 2012 environment override that connection property, it should work. Unfortunately I currently don't have a SSIS 2012 set up to test. I will try to get it up and running and give you an answer.

Your comment:





 

Copyright © Kevin Shyr

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski