Execution Patterns - SQL Server Integration Services Design Patterns - page 92

The application works when the user either enters the full path to an SSIS package
in the file system or clicks the ellipsis to browse to a SSIS package (dtsx) file. After se-
lecting a file, the full path will display in the Package Path text box. To execute the
package, click the Start button in the SSIS Package in the File System group box.
When the Start button is clicked, the form code in the btnStartFile_Click sub-
routine is executed, and it executes a single line of code that calls the bt-
nStartFileClick subroutine in the frmMainHelper module.
The btnStartFileClick subroutine first changes the form cursor to a
WaitCursor . Next it updates the txtStatus text box to display the text Executing fol-
lowed by the full path of the SSIS package in the Package Path text box. The Refresh
statement causes the form to update, displaying the WaitCursor and the message in
txtStatus. The code then creates an instance of an SSIS application ( Mi-
crosoft.SqlServer.Dts.Runtime.Wrapper.Application ) in the form
of the ssisApp variable. ssisPkg is an instance of an SSIS Package object. It is
created by calling the LoadPackage method of the SSIS Application object ( ss-
isApp ). We use the Package object's Execute method to start the SSIS package.
The remainder of the subroutine resets the form cursor and updates the txtStatus mes-
sage to indicate the package executed.
Were I to harden this code for production, I would wrap much of the code in this
subroutine in a large Try-Catch block. In the Catch section, I would reset the cursor and
update txtStatus with the error message. I like logging— a lot. In a Production-
hardened version, I would log my intention to execute the package and include the full
path displayed in the Package Path text box. I would also log the result of the attempted
execution, whether it succeeded or failed.
The code that executes an SSIS package stored in the SSIS Catalog is found in the
frmMainHelper module's btnStartCatalogClick subroutine. The code that
manages the cursor and messaging to the txtStatus text box is comparable to that found
in the btnStartFileClick subroutine.
There are a few more moving parts to an SSIS package stored in the SSIS Catalog,
shown in Figure 2-10 .