SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Write a Variable To a File Using SSIS


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.


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)

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.

Sam Vanga

Sam Vanga helps business people solve their data problems and enables them make data-driven-decisions. His specialties include data integration, data warehouse and database development, and BI design. Sam blogs at http://SamuelVanga.com.


Leave a comment on the original post [svangasql.wordpress.com, opens in a new window]

Loading comments...