Biml and SSIS Frameworks - SQL Server Integration Services Design Patterns - page 532

For this, you will use an Execute SQL task configured to execute the
log.LogApplicationFailure stored procedure. The question is: Where? The
answer is: the Parent.dtsx package's OnError event handler.
In SSDT, click the Event Handlers tab on Parent.dtsx . In the Executable drop-
down, select Parent; in the Event Handler drop-down, select OnError as shown in Fig-
ure A-11 .
Figure A-11 . Configuring the Parent package's OnError event handler
Click the Click Here to Create an “OnError” Event Handler for Executable “Par-
ent” link on the surface of the event handler to create the OnError event handler for the
Parent.dtsx package. I could walk you through building another Execute SQL task
to log the SSIS application failure; however, it's easier and simpler to just copy the Log
Application Success Execute SQL task from the bottom of the control flow and paste it
into the Parent.dtsx OnError event handler. When you do, change the name to
Log Application Failure and the SQLStatement property to
log.LogApplicationFailure .
You are now ready to test, but you have no real way to test the application failure
unless you modify a package—and that just seems tragic. You're likely going to need
to test errors after this, too. So why not build an ErrorTest.dtsx SSIS package
and add it to the SSIS application? I like this plan!
Create a new SSIS package and rename it ErrorTest.dtsx . Add a Script task
to the control flow and rename it Succeed or Fail? Change the ScriptLanguage prop-
erty to Microsoft Visual Basic 2012. Open the editor and add the Sys-
tem::TaskName and System::PackageName variables to the
ReadOnlyVariables property. Open the Script Editor and add the code shown in
Listing A-13 to SubMain() .
Listing A-13 . Code to Succeed or Fail SSIS Package
Public Sub Main()
Dim sPackageName As String
= Dts.Variables("PackageName").Value.ToString