tim laqua dot com Thoughts and Code from Tim Laqua

31Mar/091

Adding Windows Event Log Logging To Existing SSIS Packages via C#

While working with some SSIS logging, it occured to me that there's really no good way to globally apply the same logging to all packages on your server. For our production SSIS server, it would make sense to log all OnError events to the Application Event log. Then, whatever event log monitoring app you use can notify you of the package error (with details!). Thus, this app was born - an app to remove pre-existing LogProviders and add in the intended LogProvider and options.

This is really just an example of the implementation that worked for my purposes - I'm sure some of you would prefer to log elsewhere. This should be enough to get you going. Please drop a line in the Comments if you post alternative approaches.

Usage

SSISForcedLogging.Console.exe "Z:\SSIS Packages"
or
SSISForcedLogging.Console.Exe "Z:\SSIS Packages\Package1.dtsx"


For the following code, be sure to add References to:

  • Microsoft.SqlServer.ManagedDTS
  • System.configuration

Program.cs

using System;
using System.Configuration;
using Microsoft.SqlServer.Dts.Runtime;
using System.Reflection;
using System.Collections;
using System.IO;
using System.Collections.Generic;

namespace SSISForcedLogging.Console
{
    class ColumnFilter
    {
        public bool Computer;
        public bool DataBytes;
        public bool ExecutionID;
        public bool MessageText;
        public bool Operator;
        public bool SourceID;
        public bool SourceName;

        public DTSEventColumnFilter GetDTSEventColumnFilter()
        {
            DTSEventColumnFilter dtsEventColumnFilter = 
                new DTSEventColumnFilter();
            dtsEventColumnFilter.DataBytes = this.DataBytes;
            dtsEventColumnFilter.Computer = this.Computer;
            dtsEventColumnFilter.ExecutionID = this.ExecutionID;
            dtsEventColumnFilter.MessageText = this.MessageText;
            dtsEventColumnFilter.Operator = this.Operator;
            dtsEventColumnFilter.SourceID = this.SourceID;
            dtsEventColumnFilter.SourceName = this.SourceName;
            return dtsEventColumnFilter;
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            if (args.Length != 1)
            {
                System.Console.WriteLine(
                    "\nPlease specify a package path or directory\n");
                Environment.Exit(-1);
            }

            string arg = args[0];
            List packages = new List();

            if (File.Exists(arg))
                packages.Add(arg);
            else if (Directory.Exists(arg))
                packages = GetPackagesFromDirectory(arg);
            else
            {
                System.Console.WriteLine(
                    "\nInvalid package path or directory specified\n");
                Environment.Exit(-1);
            }

            foreach (string pkgPath in packages)
            {
                System.Console.WriteLine(
                    string.Format("Updating: {0}", pkgPath));
                Application dts = new Application();
                Package pkg = dts.LoadPackage(pkgPath, null);

                RemoveOldLogsFromPackage(ref pkg);
                AddLogToPackage(ref pkg);

                System.Console.WriteLine(
                    string.Format("Saving: {0}", pkgPath));
                dts.SaveToXml(pkgPath, pkg, null);
            }
        }

        public static void AddLogToPackage(ref Package Pkg)
        {
            // Add new provider
            Pkg.LoggingMode = DTSLoggingMode.Enabled;

            LogProvider log = Pkg.LogProviders.Add(
                ConfigurationManager.AppSettings["logType"]);
            log.Name = 
                ConfigurationManager.AppSettings["logName"];
            log.Description = 
                ConfigurationManager.AppSettings["logDesc"];
            Pkg.LoggingOptions.SelectedLogProviders.Add(log);

            LoggingOptions logOptions = Pkg.LoggingOptions;
            logOptions.EventFilterKind = DTSEventFilterKind.Inclusion;

            string[] events = 
                ConfigurationManager
                    .AppSettings["eventsToLog"]
                        .Split(',');
            logOptions.EventFilter = events;

            ArrayList columnList = new ArrayList();
            columnList.AddRange(
                ConfigurationManager
                    .AppSettings["columnsToLog"]
                        .Split(','));

            ColumnFilter eventColumnFilter = new ColumnFilter();

            foreach (FieldInfo field in 
                     eventColumnFilter.GetType().GetFields())
                if (columnList.Contains(field.Name))
                    field.SetValue(eventColumnFilter, true);

            logOptions.SetColumnFilter(
                "OnError", 
                eventColumnFilter.GetDTSEventColumnFilter());

        }

        public static void RemoveOldLogsFromPackage(ref Package Pkg)
        {
            // Remove existing log provider selections
            int count = Pkg.LoggingOptions.SelectedLogProviders.Count;
            for (int i = 0; i < count; i++)
                Pkg.LoggingOptions.SelectedLogProviders.Remove(i);

            // Remove existing log providers
            count = Pkg.LogProviders.Count;
            for (int i = 0; i < count; i++)
                Pkg.LogProviders.Remove(i);

            RemoveOldLogsFromExecutable(Pkg.Executables);

        }

        public static void RemoveOldLogsFromExecutable(Executables Execs)
        {
            // reflection ftw
            int count;
            foreach(Executable exec in Execs) 
            {
                foreach (PropertyInfo prop in 
                         exec.GetType().GetProperties())
                {
                    switch(prop.Name) {
                        case "LoggingMode":
                            prop.SetValue(
                                exec, 
                                DTSLoggingMode.UseParentSetting, 
                                null);
                            break;
                        case "LoggingOptions":
                            LoggingOptions opts = 
                                prop.GetValue(
                                    exec, 
                                    null) as LoggingOptions;

                            count = opts.SelectedLogProviders.Count;
                            for (int i = 0; i < count; i++)
                                opts.SelectedLogProviders.Remove(i);
                            break;
                        case "Executables":
                            Executables execs = 
                                prop.GetValue(
                                exec, 
                                null) as Executables;
                            RemoveOldLogsFromExecutable(execs);
                            break;
                    }
                }
            }
        }

        public static List GetPackagesFromDirectory(string Path)
        {
            List files = new List();

            foreach (string d in Directory.GetDirectories(Path))
                foreach (string f in GetPackagesFromDirectory(d))
                    files.Add(f);

            foreach (string f in Directory.GetFiles(Path, "*.dtsx"))
                files.Add(f);

            return files;
        }
    }
}

App.config



	
		
		
		
		
		
	   

Comments (1) Trackbacks (0)
  1. Thanks! This saved me a couple of hours of tedious manual update of lots of packages!


Leave a comment

No trackbacks yet.