Simon Rice – Blog

This short guide, my first of many I hope, will show you how to route log messages from SQL Server Integration Services, or SSIS, to a log provider like log4net or Commons Logging when running a package from a console application.

For one of my projects, we were (over)using SSIS for a number of automated data integration tasks.  It has been documented elsewhere that SSIS does have a number of shortcomings.  I’ve quite often had the nightmare of having to write out task scripts in Visual Basic* when SSIS didn’t have the right task for me.  Plus we were seriously being let down by its poor logging, especially when attempting to trace problems through SQL Server Management Studio.  We had also moved our version control rightfully from Visual SourceSafe to Subversion, but an SSIS package won’t work well with SVN as it’s literally 1 long line!

So we’ve decided that we’ll eventually re-write all of our tasks as custom C# programs (as an aside, we’re using Quartz.NET to perform internal scheduling & provide us with some simple concurrency).  Some of these tasks have been trivial to implement, but others need more time & therefore budget to move over.  However, we could benefit from improved logging from day 1 yet still use some SSIS tasks for now.

The idea here is to run an SSIS task from a C# console application & have all of the log messages from SSIS sent to a log provider like log4net or Commons Logging.  The idea was that we can log useful messages to an XML file, do away with useless debug messages like verifying a task or data flow, & have the messages popping up on a console (in colour for us!) when we’re running the program manually.

In order to do this, you need to create a project that references Microsoft.SqlServer.ManagedDTS.  After doing that you’ll need to create a class that’s derived from Microsoft.SqlServer.Dts.Runtime.LogProviderBase, as shown below:

[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Naming", "CA1724:TypeNamesShouldNotMatchNamespaces"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1001:TypesThatOwnDisposableFieldsShouldBeDisposable"),
 DtsLogProvider(DisplayName = "SSISLogProvider", Description = "Writes log entries for events to an alternative log provider", LogProviderType = "Custom")]
class SSISLogProvider : LogProviderBase
{
    protected ILog _Log = LogManager.GetLogger("SSIS");

    public override void OpenLog()
    {
        _Log.Debug("Opening logger");
    }

    public override void Log(string logEntryName, string computerName, string operatorName, string sourceName, string sourceID, string executionID, string messageText, DateTime startTime, DateTime endTime, int dataCode, byte[] dataBytes)
    {

        if (!string.IsNullOrEmpty(messageText))
        {
            string logMessage = string.Format("{0}: {1} ({2})", sourceName, messageText, logEntryName);
            switch (logEntryName)
            {
                case "OnPreExecute":
                case "OnPostExecute":
                case "OnPreValidate":
                case "OnPostValidate":
                    _Log.Debug(logMessage);
                    break;
                case "OnWarning":
                    _Log.Warn(logMessage);
                    break;
                case "OnError":
                    _Log.Error(logMessage);
                    break;
                default:
                    _Log.Info(logMessage);
                    break;
            }
        }
    }

    public override void CloseLog()
    {
        _Log.Debug("Closing logger");
    }

    public override DTSExecResult Validate(IDTSInfoEvents events)
    {
        _Log.Debug("Validating");
        return DTSExecResult.Success;
    }
}

This does look like a bit of a convoluted class, but it’s quite simple to explain. Error messages are sent to the error level, warnings are sent to the warning level, pre & post execution, validation & logger opening / closing messages are sent to the debug level and everything else is sent to info. That way you don’t have junk messages in your logs unless you really need them.

So how do you use this from your C# program?  Well, here’s a simple console application that uses Log4Net – it’s easy enough to move over to Commons Logging:

class Program
{
    static void Main(string[] args)
    {
        log4net.Config.XmlConfigurator.Configure();
        Application app = new Application();

        Package package = app.LoadPackage("mypackage.dtsx", null);

        package.LoggingOptions.EventFilterKind = DTSEventFilterKind.Inclusion;
        LogProvider log = package.LogProviders.Add(typeof(SSISLogProvider).AssemblyQualifiedName);
        package.LoggingOptions.SelectedLogProviders.Add(log);
        package.LoggingMode = DTSLoggingMode.Enabled;

        package.Execute();
    }
}

And that is pretty much all there is to it!  You can provide other details such as passwords via the instance of Package.  The only other thing to do is set app.config up for your log provider.  If there’s enough demand I’ll add a sample program with a very simple sample package for you to play with.

Happy logging!

*I am aware that in SSIS 2008, you can write tasks in C# – unfortunately I was restricted to the 2005 version.