Problem

I’ve a SSIS package. When it fails execution, I want to write a custom message including the package name and error description to a text file.

Solution

One way to accomplish this is to declare a variable, populate it with the message you want, and write it’s value to a file using a script task. Then add this script task to an OnError event handler so it executes in response to an error in the SSIS package.

1) Declare a variable: I’ve declared a package scoped variable called vFullErrorMsg.

2) Configure an OnError event handler for the package. So, the tasks in the event handler will execute when the package fails.

3) Add a script task: I renamed the task to Write To Log On Failure.

4) Edit Script Task: Add ErrorDescription,PackageName as a read only variable and vFullErrorMsg as a read write variable.

5) Use a script similar to the one below:

Public Sub Main()

‘declare a variable for error description

‘write DTS variable ErrorDescription to local variable vErrorDescription
Dim vErrorDescription As String = Dts.Variables(“ErrorDescription”).Value.ToString

‘declare a variable for package name

‘write DTS variable PackageName to local variable vPackageName
Dim vPackageName As String = Dts.Variables(“PackageName”).Value.ToString

‘Write error string to DTS variable
Me.Dts.Variables(“vFullErrorMsg”).Value =
“ERROR: ” & ” Package [" & vPackageName & "] Failed” & “. Full Description: ” & vErrorDescription

‘ write DTS variable to a file using stream writer
Using sw As StreamWriter = New StreamWriter(“D:\Documents\ErrorLog.Txt”, True)
sw.WriteLine(Dts.Variables(“vFullErrorMsg”).Value.ToString)

End Using

Dts.TaskResult = ScriptResults.Success
End Sub

With this set up, you should see the error message written to D:\Documents\ErrorLog.Txt on your local drive. You can use variables or expressions to make it work all dynamically.

:<)

Related Post: Read a flat file to a variable.

Here is my Twitter feed (@SamuelVanga) if you would like to follow me.