EzApi - Adding Event Handlers

How to add event handlers to generated packages

I had a request to give an example of adding event handlers to a generated SSIS package. The code is quite convoluted. There’s no method to initialize a DtsEventHandler, you need to cast it after adding to the package with a String describing the type of event. Here’s a list of the available events:

List of event types

Here’s a sample of how to do insert a ExecuteSQLTask to a event handler using a small console application:


using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;
using Microsoft.SqlServer.SSIS.EzAPI;
using System;

namespace ExSqlTaskExample
{
    class Program
    {
        static void Main(string[] args)
        {
            String _strConnStr = @"Data Source=localhost;Initial Catalog=AdventureWorks;Integrated Security=SSPI;Provider=SQLNCLI11.1";

            EzPackage _package = new EzPackage
            {
                Name = "Sample"
            };

            EzOleDbConnectionManager _conManager = new EzOleDbConnectionManager(_package, _strConnStr)
            {
                Name = "MyConn"
            };

            //Event Handler, no constructor :(
            DtsEventHandler _evHandler = (DtsEventHandler)_package.EventHandlers.Add("OnError");
            TaskHost myErrorHandler = (TaskHost)_evHandler.Executables.Add("Microsoft.ExecuteSQLTask");
            myErrorHandler.Name = "My package has failed";

            //Task
            ExecuteSQLTask _task = (myErrorHandler.InnerObject as ExecuteSQLTask);
            _task.SqlStatementSource = "SELECT 1";
            _task.Connection = _conManager.Name;

            //Save to disk
            _package.SaveToFile(_package.Name+".dtsx");
        }
    }
}

Wrap it up

As you can see, after seeing how it’s done it’s quite simple.

Have fun automating your packages!

 
comments powered by Disqus