Using T-SQL to execute an SSIS package

In this recipe, you are going to perform all three steps of SSIS package execution by using three special stored procedures in the SSISDB database.

This procedure can be used only on packages deployed to the SSISDB Catalog.

How to do it...

1. In SSMS, connect to the SSISDB database; that is, the user database hosting the SSISDB catalog. You can use the following command:

USE SSISDB;

Note

For your convenience, the T-SQL code needed for this chapter is provided in the Chapter06.sql script, located in the C:\SSIS2016Cookbook\Chapter06\Scripts folder.

2. Use the following query to retrieve the identifier of the environment reference, and assign the value to a variable:

DECLARE @reference_id INT;
      
SET @reference_id = (
  SELECT environment_references.reference_id
  FROM catalog.folders
    INNER JOIN catalog.projects
    ON projects.folder_id = folders.folder_id
    INNER JOIN catalog.environment_references
    ON environment_references.project_id = projects.project_id...