2

I'm trying to capture the values of about 15 variables within my sysssislog when my package executes.

I have set all the variables to true for "Raise event when variable value changes" and I understand I have to put some sort of object/code into the Event handler but I'm totally unsure as to what this should look like for the 15 variables.

Can anyone offer some examples please?

Yahfoufi
  • 2,220
  • 1
  • 22
  • 41
Philip
  • 2,460
  • 4
  • 27
  • 52

1 Answers1

3

After the RaiseChangedEvent property is set to true on the variable the OnVariableValueChanged event will need be selected for logging to SYSSSISLOG. This can be done by right-clicking the package and selecting Logging then going to the Details tab and checking the check-box for the OnVariableValueChanged event. After this click the Advanced button and check the box for each element that will be logged, for instance Computer, SourceName, etc. To see the actual value that the variable was changed to query the SSISDB.CATALOG.EVENT_MESSAGES DMV following package execution. The MESSAGE column will show the value that the variable was set as during package execution.

userfl89
  • 4,610
  • 1
  • 9
  • 17
  • Thanks for that, but this doesn't actually show the value of the variable that has changed. In the SYSSSISLOG table, it just shows the name of the variable that has changed. Any ideas? – Philip Mar 12 '19 at 10:21
  • 1
    If you deploy the package to SSISDB and execute it from there, the SSISDB.CATALOG.EVENT_MESSAGES DMV will show the actual value that the variable is changed to. When you execute the package, make sure the logging is at least at the Basic level. Sorry, I thought you were aiming to use SYSSSISLOG, but the SSISDB DMVs will provide much more comprehensive details on package statistics. – userfl89 Mar 12 '19 at 13:43
  • Thanks mate, that's exactly right, the SSISDB DMVs are awesome. – Philip Mar 12 '19 at 23:54
  • No problem, my answer is updated with this info. Glad it helped. – userfl89 Mar 13 '19 at 13:22