SQL Server Integration Services an Introduction – Part 4

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)
        {
            CreateAndSavePackage();
            LoadAndExecutePackage();
        }
        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();

            Console.WriteLine(pkgResults.ToString());
            Console.ReadKey();
        }
    }
} Conclusion
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. References
http://www.microsoft.com/technet/prodtechnol/sql/2005/mgngssis.mspx
http://msdn.microsoft.com/en-us/library/ms141711.aspx
http://msdn.microsoft.com/en-us/library/ms345167.aspx
http://www.databasejournal.com/article.php/1503191
SQL Server Books Online (BOL)

]]>