I wouldn’t say I was lucky to
have maintained some DTS packages,
but the first time I had my hand on it, I was kind of reluctant because my
interests back then were writing scripts to do SQL things and not use them
GUIs. I admit, I didn’t really understand the potential until I had my first
SSIS package created super fast in 2 hours, the tasks that otherwise would have
taken an entire day to complete.
I wasn’t sure if it was hearsay, but the APIs for creating SSIS packages
programmatically had a ramp up during the .Net framework 3.5 release. I did
some experiments in creating a few SSIS data flow and control flow items
programmatically to achieve some monotonous tasks, and in this article I am
going to show you how to create an EventHandler and write a ScriptTask inside
If you liked this article, please
take a look at my project SSISCipherBoy at https://ssiscipherboy.codeplex.com/ for SSIS Xml configuration file encryption in a few steps.
Numerous code samples are
available to create tasks programmatically, but they are hardly even
documented. If it wasn’t for the blog at http://dougbert.com/blog/post/adding-a-vsta-script-task-programmatically.aspx
and code samples at http://blogs.msdn.com/b/mattm/archive/2008/12/30/samples-for-creating-ssis-packages-programmatically.aspx,
I wouldn’t have come this for.
You would have to understand the object
model that SSIS packages follow. Focus here is on creating an EventHandler and
adding a ScriptTask to it. The below phenomena is according to Sql Server
Integration Services 2008. To keep it simple,
package has a collection of Variables, Precedence Constraints, Event
Handlers, Connection Managers, Log Providers, and Executables.
you drag and drop in to the Control Flow tab becomes an Executable. A
Control Flow executable (like Data Flow Task, FTP Task, Execute SQL Task, Script
Task) contains Event Handlers, Variables, and may contain Components
collection as well.
Event Handler has a list of events, each of which contains its own list of
Executables, Precedence Constraints and Variables.
List of Run-Time
Events – explains how events work in SSIS package. After we have understood that,
creating an Event Handler or any Control Flow or Data Flow task is as simple as
finding the right Executable, Component, and add to its collection. Everything
properties you set for an Executable is available as properties via it’s the
Using the code
We need the following references and namespaces added to our project.
C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies
C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PublicAssemblies
We will break down the task in to
- Add an "OnPreExecute" EventHandler to the package
- Add a
ScriptTask to the “OnPreExecute” EventHandler
Member declarations we need
private static Microsoft.SqlServer.Dts.Runtime.Application _ssisApplication
= new Microsoft.SqlServer.Dts.Runtime.Application();
private static Package _ssisPackage = default(Package);
private static DtsEventHandler _onPreExecute = default(DtsEventHandler);
private static Executable _eventExec = default(Executable);
private static string _targetEventName = "OnPreExecute";
Creating and Saving a Package
Microsoft.SqlServer.Dts.Runtime.Package to create a new package. SaveToXml
takes the first argument as the package
path where the second argument package
needs to be saved.
private static void CreatePackage()
_ssisPackage = new Package();
private static void SavePackage()
_ssisApplication.SaveToXml("SSISPackageCreationDemo.dtsx", _ssisPackage, null);
Adding an EventHandler
Add a new EventHandler to the packages’ EventHandlers collection. Pass in the
event name – “
OnPreExecute” as the
argument to the Add method of the EventHandlers collection. List of Event names
are available at List
of Run-Time Events.
private static void AttachEventHandler()
_onPreExecute = (DtsEventHandler)_ssisPackage.EventHandlers.Add(_targetEventName);
Adding a ScriptTask
An EventHandler contains its set
of Executables, and we are going to add a ScriptTask
to it. The way to do that is by using the STOCK moniker values. I don’t know
what it means, but suffice it to say that it is just a set of strings you could specify to obtain the
respective executable. http://msdn.microsoft.com/en-us/library/ms135956.aspx
list of stock moniker values (the list of
STOCK moniker values in msdn link is not complete). Since we are going to add a ScriptTask, our STOCK
Set the name description properties.
private static void AttachScriptTask()
_eventExec = _onPreExecute.Executables.Add("STOCK:ScriptTask");
TaskHost stTaskHost = (TaskHost)_eventExec;
ScriptTask st = (ScriptTask)stTaskHost.InnerObject;
EditScriptTask(ref st, "System.Configuration");
We have just created a ScriptTask with a name stSSISPackageCreationDemo and
we are responsible to add code files to it. Use
property of the ScriptTask instance to add code files, assembly references.
Build() method of the ScriptingEngine returns true on successful build. Save
the script to storage (I am not sure what a storage
is, but gets the job done) using the SaveScriptToStorage()
method. Remember to close the IDE.
private static bool EditScriptTask(ref ScriptTask pScriptTask, string referenceAssemblyName)
pScriptTask.ScriptProjectName = "stSSISPackageCreationDemo";
pScriptTask.ScriptLanguage = VSTAScriptLanguages.GetDisplayName("CSharp");
pScriptTask.ScriptingEngine.InitNewScript("CSharp", pScriptTask.ScriptProjectName, ".csproj");
catch (Exception eX)
Console.WriteLine(eX.Message + Environment.NewLine + eX.StackTrace);
A reference to System.Configuration is added for demonstration, you could choose to add any custom assemblies ot ignore it. I have a .cs file named scriptmain.cs in the bin folder of the project, I am just reading the file contents as string, and passing it to the
AddCodeFile method. The code file added should be error free, otherwise, the Build() method would return false indicating an unsuccessful build.
Download the source code, run the project, a package named SSISPackageCreationDemo.dtsx would be created inside the bin folder. Open the package in BIDS (VS 2008), navigate to the Event Handlers tab, a ScriptTask would have been created for us in the OnPreExecute EventHandler.
Points of Interest
If you run this project where you have never opened visual studio 2008, although you might have VS 2008, and Sql Server installed, then you would have to wait a couple of minutes until the Please wait while Windows configures Microsoft Visual Studio 2008 dialog dissapears.
The AddCodeFile method of the ScriptingEngine might throw an error that occurs sporadically, so it might me nearlt impossible to replicate. But fingers crossed, the probability of getting that error is 1 in a 100, at least that's my case. The error message is something on the lines of
VSTA Scripting Engine failed to create a local source file scriptmain.cs.
Initial version - February 24 2013