Logging package variable changes with the variable value

Question

 

We use package logging to capture the execution messages for failure troubleshooting.  One of the big things to know in a failure is what some of the package variables were set to.  I've not found any way to get the variable values included in the OnVariableValueChanged event message.  I could explicitly write them to a table when they change or are set via configs, but that would complicate the packages with misc clutter.  What I would really prefer is to have them with the log messages (and the OnVariableValueChanged event message) so that I know when they changed and to what value they changed during the execution.

 

Does any one know how to accomplish this?

Answer 1

Although I don't necessarily agree with your reasoning behind avoiding writing the variables to a table, etc...

However, with that being said, you would first set on raise change event flag to true for the variable you would like to have written to the log.  Next, create a script task in the package onVariableValueChanged Event Handler.  You can now write to the changed variable value to the log in this event by firing off an information event within the script.  Here is an example entry on how you might fire the event:

Code Snippet

Public Sub Main()
'
' Add your code here
'

Dim SubComponent As String = String.Empty
Dim InformationMessage As String = String.Empty

Try
' Get Task name
SubComponent = CType(ReadVariable("TaskName"), String)

InformationMessage = "Variable Name: " + CStr(ReadVariable("VariableName")) + vbCrLf + "Variable Value: " + CStr(ReadVariable("VariableValue"))

' Express loop success
Dts.Events.FireInformation(0, SubComponent, InformationMessage, String.Empty, 0, True)

Dts.TaskResult = ScriptResults.Success
Catch exDTS As DtsException
Dts.Events.FireError(CInt(exDTS.ErrorCode), SubComponent, exDTS.Message.ToString, String.Empty, 0)
Dts.TaskResult = ScriptResults.Failure
Catch ex As Exception
Dts.Events.FireError(0, SubComponent, ex.Message.ToString, String.Empty, 0)
Dts.TaskResult = ScriptResults.Failure
End Try

End Sub

Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object

Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try

Return result
End Function

Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
Try
Dim vars As Variables
Dts.VariableDispenser.LockForWrite(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
vars(varName).Value = varValue
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
End Sub

Hope this helps.

Answer 2

Yes, this helps a lot.

Thank you.

 

Answer 3

great this helps me a lot