SSIS Logging with Custom Messages

SSIS Logging is a great thing. I use it in every single SSIS Package I create. I normally use the SSIS log provider for SQL Server. I am not going to explain here how to set up logging for SSIS. There are plenty of other blog posts or BOL articles about that. Here all I was going to discuss how to add your own custom messages to log.

Drag the Script task control flow item on the Control Flow area and connect it to the desired component or components. Name the script task component wisely. The name of the script task will show up in the log's source column.

Double-click the script task and specify the ReadOnlyVariables if you want to display their values as your custom message. In this example I use Microsoft Visual Basic 2008 as my script language as that is my preferred coding choice because I just can't stand the curly braces of C#. :)

Click Edit Script which will bring up the script editor. Now look at the big block of comments. The information I am providing in this log is right there!

' To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, True)

This is what I used in an existing SSIS package I created.



Public Sub Main()
        Dts.Events.FireInformation(1, "Filename", Dts.Variables("User::SchemaRawfileLocation").Value, "", 0, False)
        Dts.TaskResult = ScriptResults.Success
 End Sub

So you can see that I created an Information event and my message is the value of the SchemaRawfileLocation variable.

On the msdn site you can look at the parameters to pass into the method. Not 100% sure on all the choices but my above example has been working.

These are the parameters:

FireInformation(informationCode as Integer, subComponent as String, description as String, helpFile as String, helpContext as Integer, byRef fireAgain as Boolean)

In my example:

informationCode: 1

subComponent: "FileName" (this does not seem to display in the SQL log though)

description: Dts.Variables("User::SchemaRawfileLocation").Value (the value of the variable, it's displayed in the message column)

helpFile: ""

helpContext: 0

fireAgain: False (no I do not want to fire this event again)

And there it is. Happy coding!