Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS logging, Event Handler, and OnVariableValueChanged Expand / Collapse
Author
Message
Posted Friday, November 21, 2008 3:39 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 7:38 AM
Points: 378, Visits: 432
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
Posted Tuesday, November 25, 2008 7:23 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 7:38 AM
Points: 378, Visits: 432
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
Posted Friday, May 14, 2010 4:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, May 2, 2011 11:13 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 3, 2011 11:25 AM
Points: 51, Visits: 175
See this Article about implementing custom ssis logging various ways. http://pragmaticworks.com/Products/Business-Intelligence/BIxPress/ssis-logging-auditing-monitoring.aspx
Post #1101766
Posted Monday, October 1, 2012 1:19 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 18, 2014 1:35 PM
Points: 39, Visits: 185


Thanks for linking to an ad for a commercial product! Big help!
Post #1366729
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse