SSIS logging, Event Handler, and OnVariableValueChanged

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » SSIS logging, Event Handler, and...

SSIS logging, Event Handler, and OnVariableValueChanged

Rate Topic

Display Mode

Topic Options

Author

Message

DEK46656

Posted Friday, November 21, 2008 3:39 PM

Old Hand

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 8:01 AM
Points: 313, Visits: 366

I am trying to write a generic script task that will log the changes to any variable (properly enabled, etc) from within an error handler for OnVariableValueChanged. I am trying to capture when a variable changes and record the results to the SSIS logging process (as opposed to some specialized logging that I have to create separately).

Public Class ScriptMain

	Public Sub Main()
        'System::VariableName,System::VariableValue,System::VariableDescription

        Dim emptyBytes(0) As Byte
        Dim vName As Variable = Dts.Variables("System::VariableName")
        Dim vDesc As Variable = Dts.Variables("System::VariableDescription")
        Dim vValue As Variable = Dts.Variables("System::VariableValue")

        If (vName.Value().ToString.Contains("New") = True) Then
            Dts.Events.FireWarning(CType(vValue.Value(), Integer), vName.Value().ToString, vDesc.Value().ToString & ": " & vValue.Value().ToString, "", 0)

        ElseIf (vName.Value().ToString.Contains("Missing") = True) Then
            Dts.Events.FireError(CType(vValue.Value(), Integer), vName.Value().ToString, vDesc.Value().ToString & ": " & vValue.Value().ToString, "", 0)

        Else
            Dts.Events.FireInformation(CType(vValue.Value(), Integer), vName.Value().ToString, vDesc.Value().ToString & ": " & vValue.Value().ToString, "", 0, False)

        End If

        Dts.TaskResult = Dts.Results.Success

    End Sub

End Class

The problem is that no matter what I try in the “logging” settings of the package, it will either not output anything, or will “double” the output, once with the Source “OnVariableValueChanged” and once with the name of the script component that runs inside of the event handler (screen).

You will notice that I am using one of the “Fire” Events since the “Dts.Log” method only works from a script task (and logging parameter ScriptTaskLogEntry on), not from an error handler or data flow task.

Has anyone ever done this, are they familiar with the problem, or is this a “bug” that no one has run into yet?

Beer's Law: Absolutum obsoletum
"if it works it's out-of-date"

Post #606873

DEK46656

Posted Tuesday, November 25, 2008 7:23 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 8:01 AM
Points: 313, Visits: 366

Is this so "out there" that no one really works with SSIS this way?

Beer's Law: Absolutum obsoletum
"if it works it's out-of-date"

Post #608333

Paul Knox

Posted Friday, May 14, 2010 4:24 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 21, 2010 1:55 AM
Points: 7, Visits: 2

Hi,

I've got exactly the same problem, except I can't get Dts.Log to work at all (even when I have ScriptTaskLogEntry option switched on in Logging for the script task).

Re. the FireInformation method...
Basically, I've tried setting logging options at the script level (to override those at the package level), and also just setting them at the script level. I've tried various combinations of events to capture (at both of the above levels) - and found that the FireInformation method logs the event regardless of whether the Information or ScriptTaskLogEntry options are switched on. I've tried not logging the Source Name column. I've tried various values in the parameters to the FireInformation method, including setting the subComponent parameter to the name of the script, the name of the package, an empty string, something else, etc.

In all cases, I get 2 lines logged for each call to this method - one with the script name being recorded as the Source Name, and the other with the package name being recorded as the Source Name.

Sorry I have no solution. It certainly looks like a bug!

Post #921892