Wednesday, November 17, 2021

SSIS Script Task - Write Log To Disk

  Purpose: Guaranteed logging on package error, advanced usage of Dts.VariableDispenser

public void LogResults()
  {
   bool errorOccurred = false; //in event all logging methods fail
   Variables lockedVariables = null;
   string fileName = "";
   string fileData = "";
   try
   {
    lockedVariables = null;
    Dts.VariableDispenser.LockOneForRead("OutputDirPath", ref lockedVariables);
    Dts.VariableDispenser.LockOneForRead("OutputData", ref lockedVariables);
    Dts.VariableDispenser.GetVariables(ref lockedVariables);
    fileName = (string)lockedVariables["User::OutputDirPath"].Value;
    if (fileName == null || fileName.Length == 0)
    {
     fileName = @"C:\ScriptTaskResults.log";
    }else{
     fileName = fileName.Replace(".log", string.Concat("_", DateTime.Now.ToString("yyyyMMddHHmmss"), ".log"));
     // fileName format ex: \\ssis-server01\FTP\ScriptTaskOutput_20200429011136.log
    }
    fileData = (string)lockedVariables["User::OutputData"].Value;
    if (fileData == null || fileData.Length == 0 || fileData.ToLower().Contains("system.object"))
    {
     fileData = "OutputData value is empty. Nothing to report.";
    }
    using (StreamWriter w = File.AppendText(fileName))
    {
     Log(fileData, w);
    }
    using (StreamReader r = File.OpenText(fileName))
    {
     DumpLog(r);
    }
    Dts.TaskResult = (int)ScriptResults.Success;
   }
   catch (Exception ex)
   {
    errorOccurred = true; // possible network logging issue. try to save to event log.
    using (EventLog eventLog = new EventLog("Application"))
    {
     eventLog.Source = "Application";
     eventLog.WriteEntry(string.Concat("ssis pkg output log issue. ", "\r\n", ex.Message.ToString()), EventLogEntryType.Error, 101, 1);
    }
   }
   finally
   {
    if (lockedVariables != null) lockedVariables.Unlock();
    if (errorOccurred) // fire event to store in SQL DTS log
    {
     Dts.Events.FireError(18, "ssis pkg output log issue.", "An Exception was caught during logging write and output data flush. Check the server event application log for details.", "", 0);
     Dts.TaskResult = (int)ScriptResults.Failure;
    }
   }
  }
  public static void Log(string logMessage, TextWriter w)
  {
   w.Write("\r\nLog Entry : ");
   w.WriteLine($"{DateTime.Now.ToLongTimeString()} {DateTime.Now.ToLongDateString()}");
   w.WriteLine("  :");
   w.WriteLine($"  :{logMessage}");
   w.WriteLine("-------------------------------");
  }
  public static void DumpLog(StreamReader r)
  {
   string line;
   while ((line = r.ReadLine()) != null)
   {
    Console.WriteLine(line);
   }
  }

No comments:

Post a Comment