Monday, 14 February 2011

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;
            }
        }
    }
}





1 comment:

  1. Very interesting.
    Thanks to have shared it.

    ReplyDelete

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...