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; } } } }
Very interesting.
ReplyDeleteThanks to have shared it.