Knight's Microsoft SQL Server 2012 Integration Services 24-Hour Trainer

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 ...