Pages

Monday, October 17, 2016

Log SSIS Variable Values When You Want To

Here's a quick hit for a Monday morning...
Integration Services logging levels can definitely be set up such that you can record when variables change value.  It's not the simplest, and involves setting properties on the variable, configuring logging, and adding an event handler.
If you ask me, that's a little too complicated - and doesn't accomplish what I (and several other people) seem to want - a record of variable values at specific times in package execution.  Times such as "at package startup", or "before task X", or "each iteration of a loop."

A Straightforward Option

YMMV of course.
  1. Drop a Script Task in your Control Flow where you want it.
  2. Add the variables you want to log to the "ReadOnly Variables" list in the script editor UI.
  3. Open the script itself and replace the Main the following:
  public void Main()
  {
            bool fireAgain = true;
            Dts.Events.FireInformation(0, "", "Script - Log Variables - v1.0", "", 0, ref fireAgain);

            // Log input argument values
            foreach (Variable inputVariable in Dts.Variables)
            {
                switch (inputVariable.DataType)
                {
                    case TypeCode.String:
                        Dts.Events.FireInformation(0, "", "  " + inputVariable.Name + ": " + (string)inputVariable.Value, "", 0, ref fireAgain);
                        break;
                    case TypeCode.Int32:
                        Dts.Events.FireInformation(0, "", "  " + inputVariable.Name + ": " + ((int)inputVariable.Value).ToString(), "", 0, ref fireAgain);
                        break;
                    case TypeCode.DateTime:
                        Dts.Events.FireInformation(0, "", "  " + inputVariable.Name + ": " + ((DateTime)inputVariable.Value).ToString(), "", 0, ref fireAgain);
                        break;
                    case TypeCode.Boolean:
                        Dts.Events.FireInformation(0, "", "  " + inputVariable.Name + ": " + ((bool)inputVariable.Value).ToString(), "", 0, ref fireAgain);
                        break;
                    default:
                        Dts.Events.FireInformation(0, "", "  " + inputVariable.Name + ": (unhandled type " + inputVariable.DataType.ToString() + ")", "", 0, ref fireAgain);
                        break;
                }
            }

            Dts.TaskResult = (int)ScriptResults.Success;
  }

The script automatically iterates over the list of variables you put in the script editor UI and outputs them to the log at runtime.  Copy and paste the whole script task to another package, change the list of variables in the UI, and you're done - no code edits required.

No comments:

Post a Comment