SSIS Logging of OnVariableValueChanged with variable value

Question

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

LONG · Accepted Answer · 2017-03-28 15:04:52Z

1

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

edited Mar 28, 2017 at 15:04

answered Mar 28, 2017 at 14:36

LONG

4,6302 gold badges21 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Johannes Wentu Over a year ago

thank you for your time. as I wrote in my question, I am already doing exactly that. it is logging BUT it is not passing the new value of the variable to the handler

Johannes Wentu Over a year ago

So far I didn't understand the problem but at least I found out it is possible to use breakpoints and watches in SSIS. Thank you.

Collectives™ on Stack Overflow

SSIS Logging of OnVariableValueChanged with variable value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related