“OnVariableValueChanged” Event Handler in SSIS Package

In SSIS packages, we usually deal with many variables (system variables as well as user defined variables). We make use of these variables for certain calculations or for achieving the dynamic configurations.

In case of dynamic configurations like file name/path for FTP connection manager or so, we create the variables and make them configurable using the package configurations options available.

These variables may have the static values or the dynamic values which get evaluated based on some expressions. These expressions may contain other variables for calculation; provided those are also in the scope of the variable defining the expression.

For a variable to be able to use the expressions for evaluating its value at runtime, itsEvaluateAsExpressionproperty must be marked as “TRUE” i.e. EvaluateAsExpression=TRUE which by default is “FALSE”.

 

Now, sometimes, setting the variables & using them across package is not sufficient. We may be required to keep track of the changes in the value of the variable taking place during the package execution. This may be required to log or do something; when values of certain variables get changed during the package execution.

To achieve this we can make use of one of the available event handlers in SSIS named “OnVariableValueChanged”.

This is the event which gets raised when value of the variable is changed. But to have this event raised properly for a particular variable; certain points to be kept in mind:

 

·         Property of the variable “RaiseChangeEvent” must be set to “TRUE”.

·         When the OnVariableChangedEvent is fired, the event is scoped to the same container that the variable is scoped to. The event is NOT scoped to the container that changed the variable.

·         It means that an OnVariableChangedEvent eventhandler scoped to the container that changed the variable will not get fired unless the variable is scoped to the same.

·         Also, in case of variable having the expressions (with EvaluateAsExpression=TRUE), event would not be raised when the value of the expression will be changed, rather event of its dependent variables will be raised; when the value of the variable on which this expression is dependent; is changed. So to raise the event properly for such variable; the RaiseChangeEvent property for those dependent variables must be set to TRUE and their OnVariableChangedEvent events should be handled.

 

Example:

  • MyVariable (that has RaiseChangedEvent=TRUE) is scoped to a ForEachLoop container.
  • ForEachLoop container contains a script task that changes the value of MyVariable.
  • OnVariableValueChanged eventhandler scoped to the ForEachLoop container is implemented.
  • OnVariableValueChanged eventhandler scoped to the script task is also implemented.

The OnVariableValueChanged eventhandler scoped to the ForEachLoop conatiner would fire when the value of the variable changes, but the one scoped to the script task would not – even though that is the one which caused the change.

 

Advertisements
This entry was posted in SQL Server, SSIS. Bookmark the permalink.

2 Responses to “OnVariableValueChanged” Event Handler in SSIS Package

  1. Ram says:

    Thanks for your scenarios on MSBI. I got lot of complicated logics from here.

  2. roguefem says:

    Thanks. This helped me!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s