perl5 11 - Page 140 - Developer IT

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

Search Results

Search found 10158 results on 407 pages for 'perl5 11'.

Page 140/407 | < Previous Page | 136 137 138 139 140 141 142 143 144 145 146 147 | Next Page >

- by Kevin Shyr

- by DarkJaff

- by tranhuyhung

- by Sunil Kumar Sahoo

- by Masi

- by no9

- by Ryan Rosario

- by lajos.varady(at)oracle.com

- by marc dekeyser

- by mkupfer

- by Kristin Rose

- by Deepak Arora

- by Antoinette O'Sullivan

- by Programming Noob

- by Ricardo Peres

- by thatjeffsmith

- by Rohit Gupta

- by mseika

- by mseika

- by mseika

- by mseika

- by Nicolas Montoya

- by THE

- by Pranit Bauva

- by lukasz.romaszewski(at)oracle.com

< Previous Page | 136 137 138 139 140 141 142 143 144 145 146 147 | Next Page >