During the December 2017 delivery of Expert SSIS, I was asked if there is an SSIS Catalog Logging Mode that will display the value of variables. I responded that I wasn’t aware of a logging level that accomplishes this, but then – as sometimes happens – I could not let it go. It is an excellent question. So I dug in…
Answer #1: YesBut
Here’s one way to do it: Open the Variables window in SSIS. Click the Grid Options button on the Variables window toolbar:
When the Variable Grid Options dialog displays, check the “Raise event when variable value changes” checkbox:
Click the OK button to return to the Variables window and note the new property: “Raise Change Event.” Set Raise Change Event to True:
Once deployed, the value of the variable will be logged in the SSIS Catalog at startup and each time the variable value changes:
Please note: the VariableValueChanged event is captured using the SSIS Catalog Basic Logging level.
Why isn’t this a resounding “Yes! Yes, you can log all variables values in the SSIS Catalog!”? Well, because this requires developers to open existing packages, open Variables, check the checkbox to even be able to see the property, and then change the property value – for every. single. variable.
Answer #2: Use a Script Task
You can add a Script Task to your SSIS package – similar to the Script Task shown below:
Open the Script Task Editor and click the ellipsis in the ReadOnlyVariables value textbox to open the Select Variables dialog. I like to select the System::PackageName and System::TaskName variables each time I use a Script Task for logging purposes (and I use Script Tasks for logging a lot). Then select the variable(s) for which you wish to log values:
When I design Production Data Integration solutions with SSIS, I log the values of parameters and variables at the start of each execution, and each variable used inside a Loop Container or Foreach Loop Container. Why? I don’t want to guess or dig or rely on defaults; I want the package to tell me what those values were when it executed. I consider this a best practice.
I add C# similar to that shown here to perform variable value logging:
public void Main()
{
string packageName = Dts.Variables["System::PackageName"].Value.ToString();
string taskName = Dts.Variables["System::TaskName"].Value.ToString();
string subComponent = packageName + "." + taskName;
int informationCode = 1001;
bool fireAgain = true;
int Variable0 = Convert.ToInt32(Dts.Variables["User::Variable0"].Value);
string msg = "Variable0: " + Variable0.ToString();
Dts.Events.FireInformation(informationCode, subComponent, msg, "", 0, ref fireAgain);
Dts.TaskResult = (int)ScriptResults.Success;
}
Once deployed and executed, the Information Event raised in the Script Task publishes a message consumed by SSIS Catalog Basic Logging (the default SSIS Catalog logging level) as shown below:
I know many SSIS developers are uncomfortable using Script Tasks (and Script Components). While I understand the discomfort, I urge SSIS developers to learn enough .Net (C# or VB) to become comfortable using Script Tasks and Components in SSIS. In my opinion, .Net easily doubles your data integration capabilities with SSIS.
:{>
Learn more:
Expert SSIS Training (Live, Online)
IESSIS1: Immersion Event on Learning SQL Server Integration Services (In-person)
Hello Andy – and in BIML?
Fantastic! No idea the Raise Change Event option existed. Works like a charm. Thank you.
Thanks for this. SSIS debugger is close to useless, especially when it comes to showing parameter and variable values, and executing tasks step by step. SSIS itself is also becoming more and more outdated over time.
Andy, Thanks. And since we are using BIML, the change is now widely implemented.
Just added the RaiseChangedEvent=”true” attribute to the Variable elements to track.
Found the resulting entries in both the event_messages and operation_messages SSISDB catalog views.
In the operation_messages view the rows are written with message_type = 80.
in event_messages view the rows are written with both message_type = 80 and event_name = N’OnVariableValueChanged’
That’s outstanding, Bob!
:{>
I see this as a build or buy decision as that’s a lot of work to do what BiXpress does for you.
And what about a realtime monitor GUI or SSRS reports? That’s yet another development effort since the SSIS built in reports are beyond awful.
I highly recommend testing with logging levels in SSISDB before using them at all – my tests have shown a really dramatic performance impact by turning them on – much more than Bixpress.
My best practice turning off all SSIS logging for normal runs but do the opposite (using the 2016 custom logging feature) for error events only. I only do this because on rare occasions it gives me an SSIS syntax error that occurs prior to Bixpress logging anything.
Good thoughts, Gary,
As with most things SSIS, there’s more than one way to solve this problem. In a small-enough application, everything works about the same. When application design skews to thousands of variables to track or to heavy event generation or logging, one solution usually outperforms all others.
Because of this “data engineering physics,” it’s *always* better to carry more than one tool per job in your toolbox.
With any solution – built or bought – it’s important to acknowledge people’s mileage may vary. I failed to do that in this post, so thank you for reminding me!
:{>
Look good and solution 1 works fine
Question with solution 2
Let’s just say you have 100 variables
You state that there’s a lot of with option 1
Surely there’s a huge amount of work with option 2
How do I retain this for any of the (potentially) 100 variable changes in a generic way?
Or are you saying that you log ALL 100 everytime this script runs?
Or, if they can be split up e.g. only 10 apply to a specific loop, write a specific piece of code that logs the 10 as a first step and at a convienient change pont?
Ideally, I just want to log any time any user variable changes
Thanks
Hi Damian,
One method of monitoring 100 variables is in Solution 1 (as you wrote). That is the generic way to manage tracking a large number of variable values.
I log all variables (and properties subject to override at runtime) in a Script Task at the beginning of each package execution. I log the values of variables and properties subject to changes by loop containers using a Script Task inside the loop container.
I do this because state matters – especially when troubleshooting. I would rather the log inform me of variable / property status than to execute the SSIS package with breakpoints and manually view the states of properties and variables, but I admit that’s merely a preference.
Hope this helps,
Andy
Thanks Andy
Helps a great deal and what you are saying maks sense