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