This is the twenty-first article in a series entitled Stairway to Integration Services. Previous articles in the series include:

• What is SSIS? Level 1 of the Stairway to Integration Services
• The SSIS Data Pump - Level 2 of the Stairway to Integration Services
• Adding Rows in Incremental Loads – Level 3 of the Stairway to Integration Services
• Updating Rows in Incremental Loads – Level 4 of the Stairway to Integration Services
• Deleting Rows in Incremental Loads – Level 5 of the Stairway to Integration Services
• Basic SSIS Workflow Management – Level 6 of the Stairway to Integration Services
• Intermediate SSIS Workflow Management – Level 7 of the Stairway to Integration Services
• Advanced SSIS Workflow Management – Level 8 of the Stairway to Integration Services
• Control Flow Task Errors – Level 9 of the Stairway to Integration Services
• Advanced Event Behavior – Level 10 of the Stairway to Integration Services
• Logging – Level 11 of the Stairway to Integration Services
• Advanced Logging – Level 12 of the Stairway to Integration Services
• An Overview of SSIS Variables – Level 13 of the Stairway to Integration Services
• An Overview of Project Conversion – Level 14 of the Stairway to Integration Services
• An Overview of SSIS Parameters – Level 15 of the Stairway to Integration Services
• Flexible Source Locations – Level 16 of the Stairway to Integration Services
• Multiple Flexible Source Locations – Level 17 of the Stairway to Integration Services
• Deployment and Execution – Level 18 of the Stairway to Integration Services
• A Custom Execution Method – Level 19 of the Stairway to Integration Services
• SSIS Catalog Environments – Level 20 of the Stairway to Integration Services
• SSIS Catalog Environments– Step 20 of the Stairway to Integration Services

Introduction

In the previous two installments we discussed the current design of the SQL Server Integration Services Catalog and some implications for the enterprise posed by this design. In Level 19 we extended SSISDB, the SSIS Catalog database, and examined an alternate method for executing SSIS packages in the SSIS Catalog. In Level 20, we examined SSIS Catalog Environments; the Catalog’s built-in functionality for managing and externalizing parameters. In this article, we will combine a couple scripts from the Level 19 article and add functionality to the custom.execute_catalog_package stored procedure so we can choose to execute SSIS packages synchronously or asynchronously and reference SSIS Catalog environments.

What is the Problem We Are Trying to Solve?

The question, “What is the problem we are trying to solve?” is a good way to begin or refocus any effort. The problem we are trying to solve is to make the custom.execute_catalog_package better – ensure it is more ready for Production. What can we do to make this stored procedure more ready for Production?

• Add support for executing packages serially
• Improve connections management support
• Add validation to check for the existence of the SSIS package in the SSIS Catalog

Let’s begin by combining the Transact-SQL listings from Level 19.

Combining the Level 19 Scripts

We begin by combining the current versions of Listings 1 and 2 from the Level 19 article. A combined version of the custom.execute_catalog_package stored procedure is shown in Listing 1:

Use SSISDB
go
print 'Custom Schema'
If Not Exists(Select s.name
              From sys.schemas s
                  Where s.name = 'custom')
 begin
  print ' - Creating custom schema'
  declare @sql varchar(50) = 'Create Schema custom'
  exec(@sql)
  print ' - Custom schema created'
 end
Else
 print ' - Custom schema already exists.'
print ''
print 'Custom.execute_catalog_package stored procedure'
If Exists(Select s.name + '.' + p.name
          From sys.procedures p
          Join sys.schemas s
             On s.schema_id = p.schema_id
          Where s.name = 'custom'
             And p.name = 'execute_catalog_package')
 begin
  print ' - Dropping custom.execute_catalog_package'
  Drop Procedure custom.execute_catalog_package
  print ' - Custom.execute_catalog_package dropped'
 end
print ' - Creating custom.execute_catalog_package'
go
Create Procedure custom.execute_catalog_package
   @package_name nvarchar(260)
 , @folder_name nvarchar(128)
 , @project_name nvarchar(128)
 , @use32bitruntime bit = False
 , @reference_id bigint = NULL
 , @logging_level varchar(11) = 'Basic'
As
 begin
  -- create an Intent-to-Execute
  declare @execution_id bigint
  exec [SSISDB].[catalog].[create_execution]
     @package_name=@package_name
   , @execution_id=@execution_id OUTPUT
   , @folder_name=@folder_name
   , @project_name=@project_name
   , @use32bitruntime=@use32bitruntime
   , @reference_id=@reference_id
  -- Decode and configure the Logging Level
  declare @var0 smallint =
                 Case
                    When Upper(@logging_level) = 'NONE'
                    Then 0
                    When Upper(@logging_level) = 'PERFORMANCE'
                    Then 2
                    When Upper(@logging_level) = 'VERBOSE'
                    Then 3
                 Else 1 -- Basic
                 End
  exec [SSISDB].[catalog].[set_execution_parameter_value]
     @execution_id
   , @object_type=50
   , @parameter_name=N'LOGGING_LEVEL'
   , @parameter_value=@var0
  -- Start the execution
  exec [SSISDB].[catalog].[start_execution] @execution_id
  -- Return the execution_id
  Select @execution_id As execution_id
 end
go
print ' - Custom.execute_catalog_package created.'
go

Listing 1

As you may recall, this script is written to be re-executable, producing the same results from each execution (idempotent). Executing the script in Listing 1 against the same instance of SQL Server used to work through Level 19 will produce output similar to that shown in Figure 1:

Figure 1

The script first checks for the existence of the custom schema. If the custom schema does not exist, the script will create it. If the custom schema exists, the script notifies the person executing the script, as shown in Figure 1.

The script next checks for the existence of the custom.execute_catalog_package stored procedure. If the custom.execute_catalog_package stored procedure exists, it is dropped and then re-created.

Why? This will ensure that the latest version of the stored procedure is always created.

Adding Support for Serial Execution

There are six execution parameters for SSIS package executions built into the SSIS Catalog:

• CALLER_INFO – a string value containing the login of the process / individual who started the SSIS package.
• DUMP_EVENT_CODE – a string value containing a semi-colon delimited list of event codes that will be recorded in the dump file. Works with DUMP_ON_EVENT.
• DUMP_ON_ERROR – a Boolean value set to True to produce a dump file when an error in SSIS package execution is encountered.
• DUMP_ON_EVENT – a Boolean value set to True to produce a dump file when an event is raised that is specified in the DUMP_EVENT_CODE during SSIS package execution.
• LOGGING_LEVEL – an integer value that reflects the level of logging for the SSIS package execution.
• SYNCHRONIZED – a Boolean value set to True for synchronous (serial) package execution, False for asynchronous (“fire-and-forget”) SSIS package execution.

To add support for serial SSIS package execution to the custom.execute_catalog_package stored procedure, let’s begin by adding a Boolean (bit) parameter to the signature of the custom.execute_catalog_package stored procedure – defaulted to True (1) – as shown in Listing 2:

Create Procedure custom.execute_catalog_package
   @package_name nvarchar(260)
 , @folder_name nvarchar(128)
 , @project_name nvarchar(128)
 , @use32bitruntime bit = False
 , @reference_id bigint = NULL
 , @logging_level varchar(11) = 'Basic'
 , @synchronized bit = 1
As
…

Listing 2

I credit Phil Brammer (http://www.ssistalk.com), SQL Server MVP and friend, for the following information which I first encountered on his blog post Quick Tip – Run SSIS 2012 packages synchronously and other execution parameters.

Next, modify the logic of the custom.execute_catalog_package stored procedure to apply the value for the SYNCHRONIZED execution parameter as shown in Listing 3:

  exec [SSISDB].[catalog].[set_execution_parameter_value]
     @execution_id
   , @object_type=50
   , @parameter_name=N'LOGGING_LEVEL'
   , @parameter_value=@var0
                                 
   -- Set synchronized parameter
  exec [SSISDB].[catalog].[set_execution_parameter_value]
    @execution_id = @execution_id
  , @object_type = 50
  , @parameter_name=N'SYNCHRONIZED'
  , @parameter_value = @synchronized
  -- Start the execution
  exec [SSISDB].[catalog].[start_execution] @execution_id
  -- Return the execution_id
  Select @execution_id As execution_id
 end
go
print ' - Custom.execute_catalog_package created.'
go

Listing 3

The Transact-SQL logic shown in Bold in Listing 3 calls the SSIS Catalog stored procedure, catalog.set_execution_parameter_value, passing in ‘SYNCHRONIZED’ as the parameter name along with the value sent to the custom.execute_catalog_package’s @synchronized parameter. This assigns the value of the @synchronized parameter to the execution of the SSIS Catalog package’s SYNCHRONIZED execution parameter.

This achieves the requirement listed in the first bullet above: we have added support for executing SSIS Catalog packages serially. In fact, we’ve defaulted the execution of SSIS packages to serial.

I can hear you thinking, “Why serial execution, Andy?” I’m glad you asked. Much ado has been made about the performance gains one can achieve by executing SSIS packages in parallel, and for good reason: the gains range from significant to staggering. As long as the load on the SSIS package (the amount of data present at the source) remains relatively constant – as in a daily load scenario – all’s well and good. But what happens at the end of a month, quarter, or year? Are the same SSIS packages tasked with loading 30x, 91x, and 365.242x of the daily data? If so, are there dependencies? If there are hidden dependencies, if one SSIS package must complete before a later package can execute, running in some parallel scenarios may cause a race condition. There’s a word for loading a destination from a partially-loaded source: “bad.”

Testing the Synchronized Parameter

For the first test, we execute custom.execute_catalog_package, setting the new @synchronized parameter to 0 as shown in Listing 4:

Use SSISDB
go
exec custom.execute_catalog_package
   @package_name = 'LoadTemperatureData.dtsx'
 , @folder_name = 'TestFolder'
 , @project_name = 'My_First_SSIS_Project'
 , @use32bitruntime = 0
 , @reference_id = NULL
 , @logging_level='Basic'
 , @synchronized = 0

Listing 4

The results of the test are shown in Figure 2:

Figure 2

Does LoadTemperatureData.dtsx really execute in less than 1 second (as shown in the inset execution time)? Nope. This is because the SSIS package is executing with the Synchronized execution parameter set to 0 (False). Once the SSIS package is found and started, the stored procedure returns Success.

For the second test, let’s execute custom.execute_catalog_package, setting the new @synchronized parameter to 0 as shown in Listing 5:

Use SSISDB
go
exec custom.execute_catalog_package
   @package_name = 'LoadTemperatureData.dtsx'
 , @folder_name = 'TestFolder'
 , @project_name = 'My_First_SSIS_Project'
 , @use32bitruntime = 0
 , @reference_id = NULL
 , @logging_level='Basic'
 , @synchronized = 1

Listing 5

The results of the test are shown in Figure 3:

Figure 3

Running with the Synchronized execution parameter set to 1 (True), the custom.execute_catalog_package doesn’t complete execution until the SSIS package execution completes – four seconds later.

“What If…”

If you think like me, you have a question: “What happens when SSIS package execution fails with the @synchronized parameter set to False (0)?” Let’s test that!

Testing the behavior of failure conditions is critical. Most of the time you want SSIS to fail gracefully. But you want to know what a failure looks like all of the time.

Building FailPackage.dtsx

First we need a package designed to fail. Open the My_First_SSIS_Project SSIS solution. Right-click the SSIS Packages node in Solutions Explorer as shown in Figure 4:

Figure 4

Rename the new package “FailPackage.dtsx” as shown in Figure 5:

Figure 5

Add a Script Task to the Control Flow and rename it “SCR Fail” as shown in Figure 6:

Figure 6

Open the Script Task editor as shown in Figure 7:

Figure 7

Click the “Edit Script…” to open the VSTAProjects editor. Navigate to public void Main() function and change the result line to Dts.TaskResult = (int)ScriptResults.Failure;  as shown in Figure 8:

Figure 8

Close the VSTAProjects editor and the Script Task editor. Then right-click My_First_SSIS_Project project in Solution Explorer and click Deploy, as shown in Figure 9:

Figure 9

Walk through the Integration Services Deployment Wizard, starting with the “Select Destination” page shown in Figure 10:

Figure 10

Because the project has been deployed to this instance of the SSIS Catalog in the past, there is a warning at the bottom of the Integration Services Deployment Wizard “Select Destination” page that reads, “A project with this name already exists.” Click the Next button to advance to the Review page, as shown in Figure 11:

Figure 11

Click the Deploy button to deploy the updated version of the My_First_SSIS_Project project. The Integration Services Deployment Wizard Results page displays. If all goes as hoped, the deployment will succeed and the Results page will appear as shown in Figure 12:

Figure 12

Click the Close button to close the Integration Services Deployment Wizard.

Executing the Test

The script to execute the test is:

Use SSISDB
go
exec custom.execute_catalog_package
   @package_name = 'FailPackage.dtsx'
 , @folder_name = 'TestFolder'
 , @project_name = 'My_First_SSIS_Project'
 , @use32bitruntime = 0
 , @reference_id = NULL
 , @logging_level='Basic'
 , @synchronized = 0

Listing 6

Figure 13 displays the results of the text execution of FailPackage.dtsx with the @synchronized bit set to 0:

Figure 13

Note the execution time is sub-second, reflected in the “00:00:00” shown in the inset.

The execution of the stored procedure succeeds. We designed FailPackage.dtsx to fail, though. Did it? Let’s check by opening SSMS Object Explorer and expanding the Integration Services Catalogs node, and then right-clicking the SSISDB node, hovering over Reports, and clicking “All Executions” as shown in Figure 14:

Figure 14

The All Executions report displays and reveals the execution of FailPackage.dtsx did indeed fail, as shown in Figure 15:

Figure 15

Ok. The execution of FailPackage.dtsx failed but the execution of the custom.execute_catalog_package stored procedure with the @synchronized bit set to False (0) succeeded. What happens if we set the @synchronized bit to True (1)? Let’s test that by executing the following script:

Use SSISDB
go
exec custom.execute_catalog_package
   @package_name = 'FailPackage.dtsx'
 , @folder_name = 'TestFolder'
 , @project_name = 'My_First_SSIS_Project'
 , @use32bitruntime = 0
 , @reference_id = NULL
 , @logging_level='Basic'
 , @synchronized = 1

Listing 7

Setting the @synchronized bit to True increases the execution time of the custom.execute_catalog_package stored procedure because the stored procedure does not return until FailPackage.dtsx executes (and fails). But, as seen in Figure 16, the execution of custom.execute_catalog_package again succeeds:

Figure 16

To verify the results of executing FailPackage.dtsx, let’s return to the All Executions report and refresh the report. As before, the execution of FailPackage.dtsx returns an Error, as shown in Figure 17:

Figure 17

We now know that custom.execute_catalog_package always returns success (when it finds and executes the SSIS package), whether SSIS package execution succeeds or fails. The state of the @synchronized bit in custom.execute_catalog_package – which sets the SSIS Catalog’s SYNCHRONIZED package execution parameter – doesn’t impact the result returned from custom.execute_catalog_package.

Is this the default SSIS Catalog behavior? Custom.execute_catalog_package is executing the same Transact-SQL statement generated when we right-click the SSIS package in the SSIS Catalog and click “Execute…” Way back in Level 19, that’s where we initially got the code for the custom.execute_catalog_package stored procedure. So the answer to the question, “Is this the default SSIS Catalog behavior?” is “Yes.”

“Is This Misleading?”

“It depends.” It depends on how you’re measuring the success or failure of the SSIS package execution. If you’re using the results of the execution of custom.execute_catalog_package – or executing the package from the SSIS Catalog – then yes, the results can be misleading. If you are monitoring the SSIS Catalog reports to determine the status of SSIS package execution, then no, the results are not misleading.

“Is There Another, Potentially Less Misleading, Way?”

“Yes.” Profiling the execution of an SSIS package in the SSIS Catalog using SQL Agent provides some very useful insight. To test, create a new SQL Agent Job named “Test Execute FailPackage” as shown in Fig

Figure 18

On the Steps page, add a step of the Type “SQL Server Integration Services Package,” configured to execute the FailPackage.dtsx package, as shown in Figure 19:

Figure 19

Click OK twice to close the Job Step Properties editor and the Job Properties editor.

Use SSMS to determine the database_id of the SSISDB database by executing the following Transact-SQL statement:

Select *
 From sys.databases
 Where name = 'SSISDB'

Listing 8

Your results will be different from my results, but my results are shown in Figure 20:

Figure 20

Create a new trace in SQL Server Profiler. Click the Events Selection tab and uncheck all events except the RPC:Completed and the SQL:BatchStarting events, as shown in Figure 21:

Figure 21

Check the “Show all events” and “Show all columns” checkboxes. Then click the “Column Filters…” button to display the Edit Filters window. Click the “DatabaseID” filter, expand the “Equals” node, and enter the value of the database_id field returned when you executed the query in Listing 8, as shown in Figure 22:

Figure 22

Combined, these configuration settings configure the SQL Server Profiler trace to return only “RPC:Completed” events observed in the SSISDB database. Click the OK button to close the Edit Filter window, and then click the Run button to start the trace.

Return to SSMS and right-click the “Test Execute FailPackage” SQL Agent Job as shown in Figure 23:

Figure 23

The “Test Execute FailPackage” SQL Agent Job executes and fails as shown in Figure 24:

Figure 24

SQL Server Profiler, however, has captured the RPC:Completed and SQL:BatchStarting events that occurred in the SSISDB database during the execution of the “Test Execute FailPackage” SQL Agent Job. Scrolling through the first few captured events we find an execution of the stored procedure catalog.set_execution_parameter_value that is called to set the value of the SYNCHRONIZED execution parameter to True (1), as shown in Figure 25:

Figure 25

SQL Server Profiler reveals the “SQL Server Integration Services Package” SQL Agent Job Step Type executes the SSIS package synchronously. But our tests revealed executions of packages that fail return successfully. So, how does SQL Agent know the package execution failed?

The answer is found at the very end of the trace. After the synchronous execution of the SSIS package completes, the “SQL Server Integration Services Package” SQL Agent Job Step Type executes a Transact-SQL statement to retrieve the execution Status of the package execution from the SSISDB.catalog.executions view, as shown in Figure 26:

Figure 26

If we copy this Transact-SQL statement from SQL Server Profiler and execute it in SSMS, we can see the result, as shown in Figure 27:

Figure 27

According to the MSDN topic titled catalog.executions (SSISDB Database), the status field contains an integer (int) value that represents:

The status of the operation. The possible values are created (1), running (2), canceled (3), failed (4), pending (5), ended unexpectedly (6), succeeded (7), stopping (8), and completed (9).

A value of 4 indicates the package execution failed. This is how SQL Agent knew the package execution failed, and why it reported the failure after package execution completed.

We can add the functionality we discovered to our custom.execute_catalog_package stored procedure by adding the statements in bold below:

Use SSISDB
go
print 'Custom Schema'
If Not Exists(Select s.name
              From sys.schemas s
                  Where s.name = 'custom')
 begin
  print ' - Creating custom schema'
  declare @sql varchar(50) = 'Create Schema custom'
  exec(@sql)
  print ' - Custom schema created'
 end
Else
 print ' - Custom schema already exists.'
print ''
print 'Custom.execute_catalog_package stored procedure'
If Exists(Select s.name + '.' + p.name
          From sys.procedures p
          Join sys.schemas s
             On s.schema_id = p.schema_id
          Where s.name = 'custom'
            And p.name = 'execute_catalog_package')
 begin
  print ' - Dropping custom.execute_catalog_package'
  Drop Procedure custom.execute_catalog_package
  print ' - Custom.execute_catalog_package dropped'
 end
print ' - Creating custom.execute_catalog_package'
go
Create Procedure [custom].[execute_catalog_package]
   @package_name nvarchar(260)
 , @folder_name nvarchar(128)
 , @project_name nvarchar(128)
 , @use32bitruntime bit = False
 , @reference_id bigint = NULL
 , @logging_level varchar(11) = 'Basic'
 , @synchronized bit = 1
As
 begin
  -- create an Intent-to-Execute
  declare @execution_id bigint
  exec [SSISDB].[catalog].[create_execution]
     @package_name=@package_name
   , @execution_id=@execution_id OUTPUT
   , @folder_name=@folder_name
   , @project_name=@project_name
   , @use32bitruntime=@use32bitruntime
   , @reference_id=@reference_id
  -- Decode and configure the Logging Level
  declare @var0 smallint = Case When Upper(@logging_level) = 'NONE'
                           Then 0
                           When Upper(@logging_level) = 'PERFORMANCE'
                           Then 2
                           When Upper(@logging_level) = 'VERBOSE'
                           Then 3
                           Else 1 -- Basic
                           End
  exec [SSISDB].[catalog].[set_execution_parameter_value]
     @execution_id
   , @object_type=50
   , @parameter_name=N'LOGGING_LEVEL'
   , @parameter_value=@var0
                 
   -- Set synchronized parameter
  exec [SSISDB].[catalog].[set_execution_parameter_value]
    @execution_id = @execution_id
  , @object_type = 50
  , @parameter_name=N'SYNCHRONIZED'
  , @parameter_value = @synchronized
  -- Start the execution
  exec [SSISDB].[catalog].[start_execution] @execution_id
  declare @execution_status int = (SELECT [status]
                                   FROM [catalog].[executions]
                                  WHERE execution_id = @execution_id)
  if (@execution_status = 4)
   begin
    declare @package_path varchar(512) = @folder_name + '\'
                                       + @project_name + '\'
                                       + @package_name
 declare @err_msg varchar(1000) = @package_path + ' execution failed.'
    raiserror(@err_msg, 16, 1)
   end
  -- Return the execution_id
  Select @execution_id As execution_id
 end
go
print ' - Custom.execute_catalog_package created.'
go

Listing 9

A test execution of the custom.execute_catalog_package stored procedure to start a synchronized execution of the FailPackage.dtsx SSIS package reveals the new functionality works as designed, as shown in Figure 28:

Figure 28

Executing the same test with the SYNCHRONIZED execution parameter set to False (0), reveals the stored procedure behaves in “fire and forget mode” as before, as shown in Figure 29:

Figure 29

SSIS package execution failed, but executing in non-synchronized mode means the custom.execute_catalog_package stored procedure always succeeds if the SSIS package can be found and started in the SSIS Catalog. The only way to determine the execution result of an SSIS package executed in non-synchronized mode is to examine the SSIS Catalog execution logs.

Improve Connections Management Support

The custom.execute_catalog_package stored procedure already supports connection management via SSIS Catalog Environments and References to those environments via the @reference_id parameter. If that sentence made sense, you can skip the next section. If not, please read on…

A (Very) Brief Introduction to SSIS Catalog Environments and References

SSIS Catalog Environments are collections of variables and their values. Scoped at the SSIS Catalog Folder level, Environments are available to every SSIS project and package in an SSIS Catalog. In order for an SSIS package or project to use an Environment, the package or project must reference the environment. When a project or package references an environment, a unique reference_id is created. Once a reference is created, variables may be mapped to package and project parameters and connection manager properties. Many packages and projects may reference the same environment. Packages and projects may be configured to reference 0, 1, or many environments, but a maximum of one environment may be referenced when the package or project is executed.

For more information about creating and using SSIS Catalog Environments, please see SSIS Catalog Environments – Step 20 of the Stairway to Integration Services.

Whenever possible, I like to compose Transact-SQL and stored procedure logic in a way that makes it more human-readable. My goal is to provide as much information as possible – at a glance – to the Production DBA or data integration developer that is new to the enterprise. Experience informs me that this makes for more maintainable solutions, and “more maintainable” translates into a lower cost of ownership.

An example of making the custom.execute_catalog_package more human-readable is found in the @logging_level parameter. Logging level is an enumeration:

0. None

1. Basic

2. Performance

3. Verbose

I could have simply passed a TinyInt value for the @logging_level parameter. Some argue that people configuring SSIS execution should possess this knowledge about logging level before configuring the execution of an SSIS package. While I appreciate the sentiment, I still think a better solution is making the @logging_level parameter understandable for a wider audience. Not everyone reading SSIS package execution syntax will be tasked with executing SSIS packages in the enterprise. Some may be simply auditing the documentation. If you’ve never had the pleasure of experiencing an audit of your enterprise data integration architecture and solutions, please take my word for it: You will want to do everything you can to facilitate communication of precisely what your SSIS packages do when called.

So let’s replace the @reference_id parameter in the custom.execute_catalog_package stored procedure with a parameter named @environment_name. Our stored procedure now begins as shown in Listing 10:

Create Procedure custom.execute_catalog_package
   @package_name nvarchar(260)
 , @folder_name nvarchar(128)
 , @project_name nvarchar(128)
 , @use32bitruntime bit = False
 , @environment_name sysname = NULL
 , @logging_level varchar(11) = 'Basic'
 , @synchronized bit = 1
As
…

Listing 10

SSIS Catalog Environments are referenced by SSIS Catalog Projects via a reference_id that couples an environment to a project. In the previous version of the custom.execute_catalog_package stored procedure, we supplied the reference_id value. We now supply the name of the environment (@environment_name), but we still really need the reference_id. Let’s add logic prior to creating the intent to execute that identifies the reference_id from the environment_name parameter. To accomplish this, we need to first identify the project_id. Add the following Transact-SQL at the top of the custom.execute_catalog_package stored procedure:

declare @project_id bigint
  -- Get Project ID
  Set @project_id = (Select projs.project_id 
                     From catalog.projects projs
                     Join catalog.folders fds
                       On projs.folder_id = fds.folder_id
                     Join catalog.packages pkgs
                       On projs.project_id = pkgs.project_id
                     Where fds.name = @folder_name
                       And projs.name = @project_name
                       And pkgs.name = @package_name)

Listing 11

We retrieve the project_id value by querying the catalog.projects view, joined to the catalog.folders and catalog.packages views.

Once we have the project_id, we can query the catalog.environment_references view for the project_id and environment_name. Since we removed reference_id from the custom.execute_catalog_package parameters, we need to first declare a parameter to contain this value as shown in Listing 12:

  declare @reference_id bigint
  -- Get reference_id if environment_name is NOT NULL
  If (@environment_name Is Not NULL)
   begin -- @EnvironmentName Is Not NULL
    -- Get Reference ID
    Set @reference_id = (Select reference_id
                         From catalog.environment_references
                         Where project_id = @project_id
                           And environment_name = @environment_name)
   end -- @EnvironmentName Is Not NULL

Listing 12

Let’s add a supporting parameter (@package_path) and move the declaration of another (@err_msg), and supply some validation to our Transact-SQL used to acquire the project_id value, as shown in bold in Listing 13:

  declare @project_id bigint
  declare @reference_id bigint
  declare @err_msg varchar(1000)
  declare @project_path varchar(518) = @folder_name + '\'
                                     + @project_name + '\'
                                    + @package_name
  -- Get Project ID
  Set @project_id = (Select projs.project_id 
                     From catalog.projects projs
                     Join catalog.folders fds
                       On projs.folder_id = fds.folder_id
                     Join catalog.packages pkgs
                       On projs.project_id = pkgs.project_id
                     Where fds.name = @folder_name
                       And projs.name = @project_name
                       And pkgs.name = @package_name)
  -- Validate project_id
  If (@project_id Is NULL)
   begin
    Set @err_msg = 'Cannot locate project_id for '
                  + @project_path
     RaisError(@err_msg, 16, 1)
     Return
   end

Listing 13

The @project_path parameter is used in error messages (@err_msg) to supply better feedback. If no value is located for project_id, an error is raised and the custom.execute_catalog_package stored procedure execution is halted.

Let’s next add similar validation for the reference_id value, as shown in bold in Listing 14:

  -- Get Reference ID if EnvironmentName is NOT NULL
  If (@environment_name Is Not NULL)
   begin -- @EnvironmentName Is Not NULL
    -- Get Reference ID
    Set @reference_id = (Select reference_id
                         From catalog.environment_references
                         Where project_id = @project_id
                           And environment_name = @environment_name)
-- Validate reference_id if and only if an EnvironmentName is supplied
    If (@reference_id Is NULL)
     begin -- @reference_id Is NULL
      Set @err_msg = 'Cannot locate reference_id for '
                    + @project_path + ' and environment '
                    + Coalesce(@environment_name, '[NULL]')
       RaisError(@err_msg, 16, 1)
       Return
     end -- @reference_id Is NULL
   end -- @EnvironmentName Is Not NULL

Listing 14

Altogether, our updated custom.execute_catalog_package stored procedure script should now read as shown in Listing 15:

Use SSISDB
go
print 'Custom Schema'
If Not Exists(Select s.name
              From sys.schemas s
                  Where s.name = 'custom')
 begin
  print ' - Creating custom schema'
  declare @sql varchar(50) = 'Create Schema custom'
  exec(@sql)
  print ' - Custom schema created'
 end
Else
 print ' - Custom schema already exists.'
print ''
print 'Custom.execute_catalog_package stored procedure'
If Exists(Select s.name + '.' + p.name
          From sys.procedures p
          Join sys.schemas s
             On s.schema_id = p.schema_id
          Where s.name = 'custom'
             And p.name = 'execute_catalog_package')
 begin
  print ' - Dropping custom.execute_catalog_package'
  Drop Procedure custom.execute_catalog_package
  print ' - Custom.execute_catalog_package dropped'
 end
print ' - Creating custom.execute_catalog_package'
go
Create Procedure [custom].[execute_catalog_package]
   @package_name nvarchar(260)
 , @folder_name nvarchar(128)
 , @project_name nvarchar(128)
 , @use32bitruntime bit = False
 , @environment_name sysname = NULL
 , @logging_level varchar(11) = 'Basic'
 , @synchronized bit = 1
As
 begin
  declare @project_id bigint
  declare @reference_id bigint
  declare @err_msg varchar(1000)
  declare @project_path varchar(518) = @folder_name + '\'
                                     + @project_name + '\'
                                                 + @package_name
  -- Get Project ID
  Set @project_id = (Select projs.project_id 
                     From catalog.projects projs
                     Join catalog.folders fds
                       On projs.folder_id = fds.folder_id
                     Join catalog.packages pkgs
                       On projs.project_id = pkgs.project_id
                     Where fds.name = @folder_name
                       And projs.name = @project_name
                       And pkgs.name = @package_name)
  -- Validate project_id
  If (@project_id Is NULL)
   begin
    Set @err_msg = 'Cannot locate project_id for '
                  + @project_path
     RaisError(@err_msg, 16, 1)
     Return
   end
  -- Get Reference ID if EnvironmentName is NOT NULL
  If (@environment_name Is Not NULL)
   begin -- @EnvironmentName Is Not NULL
    -- Get Reference ID
    Set @reference_id = (Select reference_id
                         From catalog.environment_references
                         Where project_id = @project_id
                           And environment_name = @environment_name)
    -- Validate reference_id if and only if an EnvironmentName is supplied
    If (@reference_id Is NULL)
     begin -- @reference_id Is NULL
      Set @err_msg = 'Cannot locate reference_id for '
                    + @project_path + ' and environment '
                         + Coalesce(@environment_name, '[NULL]')
       RaisError(@err_msg, 16, 1)
       Return
     end -- @reference_id Is NULL
   end -- @EnvironmentName Is Not NULL
  -- create an Intent-to-Execute
  declare @execution_id bigint
  exec [SSISDB].[catalog].[create_execution]
     @package_name=@package_name
   , @execution_id=@execution_id OUTPUT
   , @folder_name=@folder_name
   , @project_name=@project_name
   , @use32bitruntime=@use32bitruntime
   , @reference_id=@reference_id
  -- Decode and configure the Logging Level
  declare @var0 smallint = Case When Upper(@logging_level) = 'NONE'
                                Then 0
                                           When Upper(@logging_level) = 'PERFORMANCE'
                                Then 2
                                           When Upper(@logging_level) = 'VERBOSE'
                                Then 3
                                   Else 1 -- Basic
                                   End
  exec [SSISDB].[catalog].[set_execution_parameter_value]
     @execution_id
   , @object_type=50
   , @parameter_name=N'LOGGING_LEVEL'
   , @parameter_value=@var0
                 
   -- Set synchronized parameter
  exec [SSISDB].[catalog].[set_execution_parameter_value]
    @execution_id = @execution_id
  , @object_type = 50
  , @parameter_name=N'SYNCHRONIZED'
  , @parameter_value = @synchronized
  -- Start the execution
  exec [SSISDB].[catalog].[start_execution] @execution_id
  declare @execution_status int = (SELECT [status]
                                   FROM [catalog].[executions]
                                              WHERE execution_id = @execution_id)
  if (@execution_status = 4)
   begin
    declare @package_path varchar(512) = @folder_name + '\' + @project_name + '\' + @package_name
     set @err_msg = @package_path + ' execution failed.'
    raiserror(@err_msg, 16, 1)
   end
  -- Return the execution_id
  Select @execution_id As execution_id
 end
go
print ' - Custom.execute_catalog_package created.'
go

Listing 15

Let’s test our changes! Execute the following Transact-SQL statement to perform a test execution of the LoadTemperature.dtsx SSIS package:

Use SSISDB
go
exec custom.execute_catalog_package
     @package_name = N'LoadTemperatureData.dtsx'
   , @project_name = N'My_First_SSIS_Project'
   , @folder_name = N'TestFolder'

My test execution results are shown in Figure 30:

Figure 30

Examining the SSIS Catalog Overview report, we see the TestFolder\My_First_SSIS_Project\LoadTemperature.dtsx package was executed with no SSIS Catalog Environment supplied and no Connection Manager ConnectionString override, as shown in Figure 31:

Figure 31

We can apply what we learned from SSIS Catalog Environments – Step 20 of the Stairway to Integration Services by executing the following Transact-SQL statement. It performs a similar test execution of the LoadTemperature.dtsx SSIS package using the DEV SSIS Catalog Environment:

Use SSISDB
go
exec custom.execute_catalog_package
     @package_name = N'LoadTemperatureData.dtsx'
   , @project_name = N'My_First_SSIS_Project'
   , @folder_name = N'TestFolder'
   , @environment_name = N'DEV'

My results from this test execution are shown in Figure 32:

Figure 32

Examining the SSIS Catalog Overview report, we see the TestFolder\My_First_SSIS_Project\LoadTemperature.dtsx package was executed with the “_Environments\DEV” SSIS Catalog Environment supplied and that the Connection Manager ConnectionString was overridden, as shown in Figure 33:

Figure 33

This is not the only way to customize SSIS Catalog execution. While the code supplied in this demo is functional and could be utilized in a Production environment, I would recommend adding more validation and logging to improve maintain-ability. The code provided in Levels 19, 20, and 21 of the Stairway to Integration Services can be extended to provide even more automation and support for different execution design patterns.

This is just the beginning of what’s possible…

Conclusion

In this article, we combined a couple scripts from the Level 19 article, and added functionality to the custom.execute_catalog_package stored procedure to allow synchronous or asynchronous SSIS package execution and to allow the application of SSIS Catalog Environment Variable values (from the Level 20 article) to Referenced (pre-configured) execution properties.