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

With the exception of the column names, the log.SSISEvents table is precisely
the same design as the log.SSISErrors table. Return to SSDT and copy the Log
Error Execute SQL task from the Parent.dtsx OnError event handler. Change the
Event Handler drop-down from OnError to OnInformation and create the OnInforma-
tion event handler by clicking the link. Next, paste the contents of the clipboard onto
the OnInformation event handler surface. Open the editor and change the name of the
task to Log Event . Edit the SQLStatement property to read log.LogEvent . On
the Parameter Mapping page, change the ErrorDescription parameter name from
ErrorDescription to EventDescription . Close the Execute SQL Task Edit-
or and you are done.
But what about all that “Error” stuff in the parameter mapping? The OnInformation
event handler message is conveyed via an SSIS variable named Sys-
tem::ErrorDescription . That is not a typo . You might expect it to be In-
formationDescription , but it's not, which makes less work for me (and you).
If you execute Parent.dtsx now to test the new event logging functionality,
then you won't see any events logged. Bummer. How do you get events from SSIS?
Several tasks provide information via OnInformation events. The Data Flow task, for
example, provides lots of helpful metadata about rows read from sources and written to
destinations, and lookup cache sizes, rows, and time to populate. You can also inject
OnInformation events into the execution stream using a Script task.
I like to include Script tasks that summarize the information I have about SSIS ap-
plications and packages in SSIS framework parent packages. Let's add those now.
Drag a Script task onto the Parent.dtsx package's control flow and rename it
Log Application Variables . Open the editor and change the ScriptLanguage to Mi-
crosoft Visual Basic 2012. Add the following variables to the ReadOnlyVariables
property:
• System::TaskName
• System::PackageName
• User::AppInstanceID
• User::ApplicationName
Edit the script and place the code shown in Listing A-18 in Sub Main() .
Listing A-18 . Raising an Information Event from a Script Task