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


SSIS logging, Event Handler, and OnVariableValueChanged


SSIS logging, Event Handler, and OnVariableValueChanged

Author
Message
DEK46656
DEK46656
Right there with Babe
Right there with Babe (764 reputation)Right there with Babe (764 reputation)Right there with Babe (764 reputation)Right there with Babe (764 reputation)Right there with Babe (764 reputation)Right there with Babe (764 reputation)Right there with Babe (764 reputation)Right there with Babe (764 reputation)

Group: General Forum Members
Points: 764 Visits: 581
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"
DEK46656
DEK46656
Right there with Babe
Right there with Babe (764 reputation)Right there with Babe (764 reputation)Right there with Babe (764 reputation)Right there with Babe (764 reputation)Right there with Babe (764 reputation)Right there with Babe (764 reputation)Right there with Babe (764 reputation)Right there with Babe (764 reputation)

Group: General Forum Members
Points: 764 Visits: 581
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"
Paul Knox
Paul Knox
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 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!
SSIS Guy
SSIS Guy
Old Hand
Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)

Group: General Forum Members
Points: 317 Visits: 175
See this Article about implementing custom ssis logging various ways. http://pragmaticworks.com/Products/Business-Intelligence/BIxPress/ssis-logging-auditing-monitoring.aspx
phil.vacca
phil.vacca
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 188


Thanks for linking to an ad for a commercial product! Big help!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search