Logging the conditions within a conditional split transformation (SSIS in SQL Server 2012 and 2016)

Question

I'm struggling to find out how we can store the conditions inside a conditional split transformation when we run a package. I need a snapshot of the expressions (the whole expressions, not only parameters inside them).

The closest we have got is when an error triggered the SSIS logging and stored the context in the table event_message_context, but we can't figure out how to trigger this logging without an error. We don't need it specifically to come from the built-in logging, we can surely use a script task if you have any suggestions. We run both SQL server 2012 and 2016.

Answer 1

If you use variables or parameters in the Conditional Split, you can log the values of these. There are several ways to do this, either insert the values into a custom logging table or use a Script Task to capture these. The following example uses the Dts.Events.FireInformation method from a C# Script Task. Under the Basic logging mode, this will be logged in the SYSSSISLOG table, assuming that you've chosen this as a log provider.

string taskName = Dts.Variables["System::TaskName"].Value.ToString();
string varName = Dts.Variables["User::YourVariableName"].Value.ToString();
int infoCode = 99;
bool fireAgain = true; 
string loggedMsg = "Value of Conditional Split Variable:  " + varName;

Dts.Events.FireInformation(infoCode, taskName, loggedMsg, "", 0, ref fireAgain);