SQL Server Integration Services an Introduction – Part 4

This article will feature a demonstration using the SSIS API. This example enables a user to write code using the SSIS API to create a package, add a task and save it on the file system (the same exercise described in scenario 1 of SSIS Designer section). Later the package will be loaded from the file system and executed, programmatically this time.

SSIS provides an API object model, which can be used with any programming language to create an SSIS package programmatically. Why create a package programmatically if it can be done using SSIS Designer? Consider the following scenario:

Though a package with multiple Data Flow Tasks and multiple data flows in a single data flow task can be created. The mapping between source and destination during runtime is unable to be changed. The source, destination and column mapping metadata can’t be changed while executing a package. By building a generic loading package enables data to be loaded from any data source to any destination as long as the metadata is known; thus creating a self-modifying package.

In the scenario mentioned above, SSIS API object model can be used to write code in C#/VB .Net etc to create package programmatically on the fly and can execute it.

Here are some namespaces/assemblies which are frequently used while creating packages programmatically.

Below is an example of using SSIS API, writing code to create a package, add a task to it, save it on the file system. Later the package can be loaded from the file system and executed.

using System;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Tasks.ExecuteProcess;

namespace SSIS_API_Programming
    class Program
        static void Main(string[] args)
        private static void CreateAndSavePackage()
            Package pkg = new Package();
            pkg.Name = “MySSISAPIExamplePackage”;
            //Adding ExecuteProcessTask to Package
            //STOCK is moniker which is used most often in the
            //Microsoft.SqlServer.Dts.Runtime.Executables.Add(System.String) method
            //though you can specify a task by name or by ID
            Executable exec = pkg.Executables.Add(“STOCK:ExecuteProcessTask”);

            //TaskHost class is a wrapper for every task
            TaskHost thExecuteProcessTask = exec as TaskHost;
            thExecuteProcessTask.Name = “Execute Process Task”;

            //Set relevant properties of the task
            ExecuteProcess execPro = (ExecuteProcess)thExecuteProcessTask.InnerObject;
            execPro.Executable = @”C:WindowsSystem32notepad.exe”;
            execPro.WorkingDirectory = @”C:WindowsSystem32″;

            Application app = new Application();
            //Save the package on file system, you can chooese to save on SQL Server as well
            app.SaveToXml(@”D:ExecuteProcess.dtsx”, pkg, null);
        private static void LoadAndExecutePackage()
            Package pkg;
            Application app;
            DTSExecResult pkgResults;

            app = new Application();
            //Load the package from file system, you can chooese to load from SQL Server as well
            pkg = app.LoadPackage(@”D:ExecuteProcess.dtsx”, null);
            //Execute the package
            pkgResults = pkg.Execute();


In the first article SSIS was described as a platform for building high performance data integration and workflow solutions. To achieve this, it uses a separate data flow engine from Runtime engine and does multithreading (allowing multiple executables/data flow to run in parallel). Also different ways to create SSIS packages and different kind of SSIS components were explained. The following article, will contain more about the features and properties of SSIS; Event Logging, Event Handlers, Transaction Support, Checkpoint Restart-ability and SSIS validation process.

SQL Server Books Online (BOL)


No comments yet... Be the first to leave a reply!