CODEWITHSUNDEEP
variablesloggingssis
Johannes Wentu
Johannes Wentu

Reputation: 989

SSIS Logging of OnVariableValueChanged with variable value

I am trying to log all changes in variable values in a SSIS generated with BIML. I managed to create an event handler that writes everytime a variable changes its value. When I log I use a parameter whose value I set to "System.VariableValue". I pass this parameter (togheter with variableName and PackageName) to a StoredProc and i write in a log table. My problem is that often (but NOT always) it seems the parameter does not has any value. I see a new line in the DB Log table so this means the evnt is correctly raised and handled BUT it seems the parameter is empty. The strangest thing is that sometimes values are logged correctly but not always, not for the same variables, not for the same packages, rather, in a quite random fashion.

Could it be a problem the fact that several variables could change value almost at the same time (some contention on the DB) ? I doubt it, because the row itself gets written on the DB. I even tried to write, as a value, something like 'New value = ' + ? that is, appending the parameter value to a fixed string. The fixed part gets written correctly but.. no value. The name of the variable that changed value is always written correctly.

Any idea what this could be due to?

As a workaround I tried to use the ready-made logging facility of SSIS but in this case in the message column of the SYSSSISLOG table i can just read the name of the variable that changed, not its new value.

thankx

Upvotes: 1

Views: 1749

Answers (1)

LONG
LONG

Reputation: 4610

You could use Event Handler to do that. Go to the Variables page, go to Variable Grid Options, check Raise event when variable value changes, and there should be one more option appear for those variables, which is Raise Change Event, default to False, change to True for those variables that you need to track the changes (log). And put a logging task into Event handler

UPDATE the new line could be the value of parameter has been reset, and that value, most likely equal to blank or whitespace, but still, that is recognized as value change.

if you are not very sure when that happened, you could set a Breakpoint to certain task and add watch window to see how the value change or whether the value will hit blank in the middle of your process

Upvotes: 1

Related Questions