Script Task and Component Logging

Case
I want to add logging to my Script Tasks and Script Components. How do I implement that?

Solution
You can raise events in the Script Task / Script Component and those events can be logged by SSIS. This article will explain the event raising.

Script Component
The Script Component has the following events:

• FireCustomEvent
• FireError
• FireInformation
• FireProgress
• FireWarning

Fire Event in Script Component

You can raise an error event with the following code

//C# code 
bool pbCancel = false;
this.ComponentMetaData.FireError(0, "myScriptComponent", "An error occurred.", "", 0, out pbCancel);

But you can also combine the event raising with the try catch statements from .Net:

//C# code 
try
{
    // your code
}
catch(Exception e)
{
    bool pbCancel = false;
    this.ComponentMetaData.FireError(0, "myScriptComponent", "An error occurred: " + e.Message, "", 0, out pbCancel);
}

You can even combine that with SSIS System Variables:

//C# code
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    // Declare script variable
    private string myTask;

    public override void PreExecute()
    {
        base.PreExecute();
        // Fill script variable with SSIS variable
        // Don't forget to add the system variable
        // to the ReadOnlyVariables in the Script
        // Component.
        myTask = Variables.TaskName; 
    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        try
        {
            // Throwing exception for testing
            // Add your own code here.
            throw new ArgumentNullException();
        }
        catch (Exception e)
        {
            // Raise event so it can be logged by SSIS
            bool pbCancel = false;
            this.ComponentMetaData.FireError(0, myTask, "An error occurred: " + e.Message.ToString(), "", 0, out pbCancel);
        }
    }
}

Script Task
The Script Task has the following events:

• FireBreakpointHit
• FireCustomEvent
• FireError
• FireInformation
• FireProgress
• FireQueryCancel
• FireWarning  

Fire Event in Script Task

You can combine the event raising with the try catch statements from .Net and the SSIS System Variables:

//C# code 
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_c9f1ec34c2ee4dbbb0bf0b0db3f3ae58.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        public void Main()
        {
            // Don't forget to add the system variable to the ReadOnlyVariables in the Script Task
            bool fireAgain = true;
            Dts.Events.FireInformation(0, Dts.Variables["System::TaskName"].Value.ToString(), "Starting", string.Empty, 0, ref fireAgain); 
            try
            {
                // Throwing exception for testing
                // Add your own code here.
                // Dts.TaskResult = (int)ScriptResults.Success;
                Dts.Events.FireWarning(0, Dts.Variables["System::TaskName"].Value.ToString(), "About to crash", string.Empty, 0);
                throw new ArgumentNullException();
            }
            catch (Exception e)
            {
                // Raise event so it can be logged by SSIS
                Dts.Events.FireError(0, Dts.Variables["System::TaskName"].Value.ToString(), "An error occurred: " + e.Message.ToString(), "", 0);
                Dts.TaskResult = (int)ScriptResults.Failure;
            }
        }
    }
}