SSIS - Programmatically create a ScriptTask inside an EventHandler using C#

• Download SSISPackageCreation-noexe.zip - 19.1 KB
• Download SSISPackageCreation.zip - 23.5 KB

Introduction

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 it.

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.

Background

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,

• A package has a collection of Variables, Precedence Constraints, Event Handlers, Connection Managers, Log Providers, and Executables.
• Anything 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.
• An 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 API.

Using the code

We need the following references and namespaces added to our project.

Project References:

C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies

Microsoft.SqlServer.DTSPipelineWrap.dll
Microsoft.SQLServer.ManagedDTS.dll
Microsoft.SqlServer.ScriptTask.dll
Microsoft.SqlServer.VSTAScriptingLib.dll

C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PublicAssemblies
Microsoft.VisualStudio.Tools.Applications.DesignTime.v9.0.dll

Namespace imports:

using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Tasks.ScriptTask;
using Microsoft.SqlServer.VSTAHosting;

We will break down the task in to four steps.

1. Create a package
2. Add an "OnPreExecute" EventHandler to the package
3. Add a ScriptTask to the “OnPreExecute” EventHandler
4. Save the package

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

Instantiate the 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 moniker is STOCK:ScriptTask. Set the name description properties.

private static void AttachScriptTask()
        {
            _eventExec = _onPreExecute.Executables.Add("STOCK:ScriptTask");
            TaskHost stTaskHost = (TaskHost)_eventExec;
            stTaskHost.Properties["Name"].SetValue(stTaskHost, "stSSISPackageCreationDemo");
            stTaskHost.Properties["Description"].SetValue(stTaskHost, "stSSISPackageCreationDemo");
            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 the ScriptingEngine 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");
            pScriptTask.ScriptingEngine.ShowDesigner(false);
            pScriptTask.ScriptingEngine.AddProjectReference(referenceAssemblyName);
            try
            {
                //provide the name of the code file and it's contents
                pScriptTask.ScriptingEngine.AddCodeFile("ScriptMain.cs", File.ReadAllText("scriptmain.cs"));
                if (!pScriptTask.ScriptingEngine.Build())
                    return false;
                pScriptTask.ScriptingEngine.SaveScriptToStorage();
            }
            catch (Exception eX)
            {
                Console.WriteLine(eX.Message + Environment.NewLine + eX.StackTrace);
            }
            finally
            {
                pScriptTask.ScriptingEngine.CloseIDE(false);
            }
            return true;
        }

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.

History

• Initial version - February 24 2013