locked
Logging package variable changes with the variable value RRS feed

  • 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?

    Tuesday, December 2, 2008 1:15 AM

Answers

  • 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.
    Tuesday, December 2, 2008 1:39 AM

All replies

  • 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.
    Tuesday, December 2, 2008 1:39 AM
  • Yes, this helps a lot.

    Thank you.

     

    Tuesday, December 2, 2008 11:42 PM
  • great this helps me a lot
    Monday, January 21, 2013 3:13 PM