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.