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:
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!