Chapter 58

Running Packages in T-SQL and Debugging Packages

In the project deployment model, you can run packages in T-SQL and in Windows PowerShell. Doing this is contingent on your having turned on CLR when you created the SSIS catalog. One challenge to running packages in T-SQL is that you don’t receive output on whether or not execution of the package was successful. This challenge is addressed in this lesson by discussing how you can use the runtime dashboard to diagnose package failures and to see execution statistics about your packages.

Running the Package

The ability to run packages in T-SQL is a game changer for those using SSIS. In the past, when you opened Management Studio from your desktop and executed the package, the package would run on your desktop, not the server. This meant that all the files had to be placed on whatever machine was running the package, not the actual server. When you run packages in SSIS 2012, the server does the actual execution because you can use T-SQL to run the package.

Executing a package in T-SQL enables you to integrate SSIS into your stored procedure or program in a much easier way than you could before. When you run the set of stored procedures that executes the package, it runs the packages as an asynchronous process and does not wait for a success or failure response. Executing the package involves creating an execution thread using the catalog.create_execution stored procedure. Then, you set any parameters using the catalog.set_execution_parameter_value stored procedure. Finally, you execute the package with the catalog.start_execution stored procedure. All of these stored procedures are in the ssisdb database.

The catalog.create_execution stored procedure’s job is to load the package and prepare it for execution. When you run the stored procedure, it returns a variable called @execution_id, which you will need later in the other stored procedures you use. The template for the stored procedure looks like this:

create_execution [ @folder_name = folder_name
     , [ @project_name = ] project_name
     , [ @package_name = ] package_name
  [  , [ @reference_id = ] reference_id ]
  [  , [ @use32bitruntime = ] use32bitruntime ]
     , [ @execution_id = ] execution_id OUTPUT

Most of the input variables are self-explanatory based on their names, but the one that may need a little explaining is the @reference_id, which refers to the folder’s environment ID number and is used only if the environment is required. You can find the @reference_id environment parameter by querying the catalog.environment_references view in the ssisdb database. A completed example is shown here:

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] 
  @package_name=N'2-OtherFeature.dtsx', 
  @execution_id=@execution_id OUTPUT, 
  @folder_name=N'EDW', 
  @project_name=N'ExpeditionDenali', 
  @use32bitruntime=False, 
  @reference_id=1

It’s critical that you capture the @execution_id for use in the upcoming stored procedures. The first place you’ll use it is in the catalog.set_execution_parameter_value stored procedure. This stored procedure enables you to set optional parameters into your SSIS package:

EXEC [SSISDB].[catalog].[set_execution_parameter_value]
  @execution_id, 
  @object_type=50, 
  @parameter_name=N'LOGGING_LEVEL', 
  @parameter_value=1

The last stored procedure to run in the batch is catalog.start_execution, which simply executes the package based on the @execution_id variable, as shown in the following code:

EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO

The complete example is shown here in one batch:

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] 
@package_name=N'2-OtherFeature.dtsx', 
@execution_id=@execution_id OUTPUT, 
@folder_name=N'EDW', 
@project_name=N'ExpeditionDenali', 
@use32bitruntime=False, 
@reference_id=1

Select @execution_id
DECLARE @var0 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
@execution_id,  
@object_type=50,
@parameter_name=N'LOGGING_LEVEL',  
@parameter_value=@var0

EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO

What’s returned from this batch is simply a number that represents the execution ID. You may want this later when you want to debug if anything has gone wrong in the package. What is not returned, though, is whether or not the package was successful.

Debugging When Something Goes Wrong

When something goes wrong with a package execution in Management Studio or T-SQL, you won’t know unless you have logging turned on or you go into the execution reports. You can access the execution reports by right-clicking the SSIS catalog folder and selecting Reports > Standard Reports > Integration Services Dashboard. This dashboard (shown in Figure 58-1) gives you high-level details of the package’s success.

Figure 58-1

If you had any failures in the past 24 hours, they’d appear at a high-level in the Connection Information area at the bottom of the report. You can also click one of the numbers (such as the number 5 in Figure 58-1, which shows the successfully run packages) to see an update on the packages that have successfully run in the past 24 hours. After looking at the latest run report, you can view an overview of a given execution by clicking the Overview link in the Execution report (see Figure 58-2). The Overview report gives you the details of what happened in the package’s execution (Figure 58-3).

Figure 58-2

You can see the complete details of the execution by clicking All Messages (see Figure 58-2). Lastly, you can see the performance of the package under Execution Performance (again, see Figure 58-2), which shows you the last 10 runs of the package and the duration of the runs.

Figure 58-3

Try It

In this Try It, you learn how to execute a package of your choice through T-SQL. After this lesson, you should be able to integrate package execution into a stored procedure.

You can download the sample code for this lesson from http://www.wrox.com.

Lesson Requirements

Find a package that you’ve already deployed to your database in the project deployment model and try to execute it in T-SQL. The sample package used in this example is the 2-OtherFeature.dtsx package found in the ExpeditionDenali project you deployed in earlier lessons. You can also download and deploy it (as part of the Lesson 53 download) from this book’s companion website at www.wrox.com.

Hints

• Use the create_execution and start_execution stored procedures in the catalog schema of the SSISDB database.

Step-by-Step

USE SSISDB
GO
Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution]
    @package_name=N'2-OtherFeature.dtsx', 
    @execution_id=@execution_id OUTPUT, 
    @folder_name=N'EDW', 
    @project_name=N'ExpeditionDenali', @use32bitruntime=False, 
    @reference_id=1
  Select @execution_id

EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO