Load and Execute SSIS Packages in Microsoft Windows

• Download source code - 395 KB
• Download SQL script - 0.5 KB

Introduction

This article introduces a method to load and execute SSIS packages in Microsoft Windows with a demo WPF application.

Background 

IT professionals who are familiar with SSIS packages will know that there are two ways of running SSIS packages. When developing an SSIS package in Visual Studio, we can run it in debugging mode. When we finish the development of an SSIS package, we can install it in SQL Server and run it. We can run the SSIS package in SQL Server either as a task or as a scheduled SQL job.

It is also very common that we do not have the source project in Visual Studio, or we do not have a SQL Server to execute an existing SSIS package. In this case, it will be nice that we can have a desktop application that allows us to load the package and run it.

If we take a look at the .NET namespace "Microsoft.SqlServer.Dts.Runtime" implemented in "Microsoft.SQLServer.ManagedDTS.dll" in the .NET Framework, we will find that Microsoft has provided all the tools to load and execute SSIS packages in our own applications. This article is to introduce a method to execute SSIS packages in a Windows application using the tools provided. "dtexe" is a good utility, but the command line utility is not very easy to use.

This article is not intended to give a demo on how to create and use SSIS packages. If you are not familiar with SSIS packages, this is a very good reference link.

The demo Visual Studio solution in this article is developed in Visual Studio 2008 and SQL Server 2008.

Overview of the Visual Studio Solution

The following picture shows the three projects organized in the demo solution called "SSISWindowsLauncher" in the Solution Explorer:

One of the projects is a .NET Class Library "SSISWindowsLibrary" that wraps the functions provided by the namespace "Microsoft.SqlServer.Dts.Runtime". This library is used to load, execute, and report the execution status of the SSIS packages in the WPF project "SSISWindowsLauncher". In order to test the WPF application "SSISWindowsLauncher", I also developed a simple SSIS package "SamplePackage.dtsx" in the Integration Service Project "SampleSSIS".

I will give some detailed explanations on these projects, starting with the Class Library "SSISWindowsLibrary".

The Class Library "SSISWindowsLibrary"

The class library implements two classes "DTSPackage" and "SSISEventListener". The class "DTSPackage" is the main class to load and execute SSIS packages, and the class "SSISEventListener" inherits from "DefaultEvents" in the "Microsoft.SqlServer.Dts.Runtime" namespace to provide runtime information to the application that executes the SSIS package.

The following is the implementation of the class "DTSPackage":

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
 
namespace SSISWindowsLibrary
{
    public enum DTSPackageStatus
    {
        Empty, LoadFailed, Loaded
    };
 
    public class DTSPackage
    {
        private string _PkgLocation;
        private DTSPackageStatus _Status;
        private Package _Pkg;
        private Microsoft.SqlServer.Dts.Runtime.Application _app;
 
        public string PkgLocation
        {
            get { return _PkgLocation; }
        }
 
        public DTSPackageStatus PackageStatus
        {
            get { return _Status; }
        }
 
        public DTSPackage()
        {
            _PkgLocation = null;
            _Status = DTSPackageStatus.Empty;
            _Pkg = null;
            _app = new Microsoft.SqlServer.Dts.Runtime.Application();
        }
 
        private void DisposePackage()
        {
            if (_Pkg != null)
            {
                _Pkg.Dispose();
                _Pkg = null;
            }
        }
 
        public void ClearPackage()
        {
            _PkgLocation = null;
            _Status = DTSPackageStatus.Empty;
            DisposePackage();
        }
 
        public void LoadPackage(string PkgLocation)
        {
            _PkgLocation = PkgLocation;
 
            if (PkgLocation != null)
            {
                DisposePackage();
                try
                {
                    _Pkg = _app.LoadPackage(PkgLocation, null);
                    _Status = DTSPackageStatus.Loaded;
                }
                catch
                {
                    _Status = DTSPackageStatus.LoadFailed;
                }
            }
        }
 
        public void SetPakageConnections(System.Collections.Hashtable ConnectionCollection)
        {
            if (_Status == DTSPackageStatus.Loaded)
            {
                Connections connections = _Pkg.Connections;
                for (int Idex = 0; Idex < connections.Count; Idex++)
                {
                    ConnectionManager connection = connections[Idex];
                    string ConName = connection.Name;
 
                    if (ConnectionCollection.Contains(ConName))
                    {
                        connection.ConnectionString = 
                            ConnectionCollection[ConName].ToString();
                    }
                }
            }
        }
 
        public System.Collections.Hashtable GetPackageConnections()
        {
            System.Collections.Hashtable ConnectionCollection = 
                               new System.Collections.Hashtable();
 
            if (_Status == DTSPackageStatus.Loaded)
            {
                Connections connections = _Pkg.Connections;
                for (int Idex = 0; Idex < connections.Count; Idex++)
                {
                    ConnectionManager connection = connections[Idex];
                    string ConName = connection.Name;
                    string ConStr = connection.ConnectionString;
 
                    ConnectionCollection.Add(ConName, ConStr);
                }
            }
 
            return ConnectionCollection;
        }
 
        public DTSExecResult Execute()
        {
            return _Pkg.Execute();
        }
 
        public DTSExecResult Execute(SSISEventListener Listerner)
        {
            return _Pkg.Execute(null, null, Listerner, null, null);
        }
 
        ~DTSPackage()
        {
            DisposePackage();
        }
    }
}

To load an SSIS package, we can simply create an object of the class "DTSPackage" and use the "LoadPackage" method to load the package by providing a path to the "dtsx" file. To execute the package, simply call the method "Execute" .

The class "DTSPackage" also provides two methods "GetPackageConnections" and "SetPakageConnections". These two methods can be used to view and change the connection strings for the connections in the SSIS package after the package is loaded into the application.

There are two ways of calling the "Execute" method. If you do not care about the details about the package execution, call it without any parameter. If you want to know the details, you can create an "SSISEventListerner" object and pass it to the "Execute" method. When finishing the execution, the listener object can provide us the details of the execution.

The "SSISEventListerner" class is implemented as follows:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
 
namespace SSISWindowsLibrary
{
    public class SSISEventListener : 
                 Microsoft.SqlServer.Dts.Runtime.DefaultEvents
    {
        private System.Data.DataTable _EventLogTable;
        private int _EventCount;
 
        public SSISEventListener()
        {
            _EventCount = 0;
  
            _EventLogTable = new System.Data.DataTable();
            _EventLogTable.Columns.Add("Status", 
                 System.Type.GetType("System.Int16"));
            _EventLogTable.Columns.Add("Event Sequence", 
           	System.Type.GetType("System.Int16"));
            _EventLogTable.Columns.Add("Time", 
                System.Type.GetType("System.DateTime"));
            _EventLogTable.Columns.Add("SSIS Execution Status", 
           	System.Type.GetType("System.String"));
        }
 
        public int EventCount
        {
            get { return _EventCount; }
        }
 
        public System.Data.DataTable EventLogTable
        {
            get { return _EventLogTable; }
        }
 
        public override bool OnError(DtsObject source, int errorCode, 
               string subComponent, string description, string helpFile, 
               int helpContext, string idofInterfaceWithError)
        {
            _EventCount++;
 
            string[] LogData = { "2", _EventCount.ToString(), 
                                 System.DateTime.Now.ToLongTimeString(), 
           	description };
            _EventLogTable.Rows.Add(LogData);
 
            return base.OnError(source, errorCode, subComponent, 
                                description, helpFile, 
                                helpContext, idofInterfaceWithError);
        }
 
        public override void OnInformation(DtsObject source, int informationCode, 
               string subComponent, string description, string helpFile, 
               int helpContext, string idofInterfaceWithError, ref bool fireAgain)
        {
            _EventCount++;
 
            string[] LogData = { "0", _EventCount.ToString(), 
                System.DateTime.Now.ToLongTimeString(), description };
                _EventLogTable.Rows.Add(LogData);
 
            base.OnInformation(source, informationCode, subComponent, 
                               description, helpFile, helpContext, 
           	idofInterfaceWithError, ref fireAgain);
        }
 
        public override void OnWarning(DtsObject source, int warningCode, 
               string subComponent, string description, string helpFile, 
               int helpContext, string idofInterfaceWithError)
        {
            _EventCount++;
 
            string[] LogData = { "1", _EventCount.ToString(), 
                     System.DateTime.Now.ToString(), description };
            _EventLogTable.Rows.Add(LogData);
 
            base.OnWarning(source, warningCode, subComponent, description, helpFile, 
          	helpContext, idofInterfaceWithError);
        }
    }
}

The "SSISEventListener" class inherits from the class "DefaultEvents". My implementation only keeps some of the events. If you feel that you need more information about the execution of the SSIS packages, you can change my implementation and add the rest of the events to it.

The WPF Application "SSISWindowsLauncher"

The WPF application "SSISWindowsLauncher" uses the Class Library "SSISWindowsLibrary". Users can use this application to browse and load an SSIS package, view the connection strings of the connections in the package, and possibly change the connection strings, execute the SSIS package, and view the execution status. The main functional part of the application is built in "WinMain.xaml" and its code-behind C# file.

"WinMain.xaml" is listed as follows:

<Window x:Class="SSISWindowsLauncher.WinMain"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:kit="http://schemas.microsoft.com/wpf/2008/toolkit"
    xmlns:WF="clr-namespace:System.Windows.Forms;assembly=System.Windows.Forms"
    Background="#E0E5FF" BorderThickness="1" BorderBrush="AliceBlue"
    Title="SSIS Package Windows Launcher" WindowState="Maximized" 
    FontFamily="Verdana" FontSize="12">
    
    <Grid Margin="10, 10, 10, 10">
        <Grid.RowDefinitions>
            <RowDefinition Height="40"/>
            <RowDefinition Height="155"/>
            <RowDefinition Height="*"/>
            <RowDefinition Height="40"/>
        </Grid.RowDefinitions>
        
        <Grid Grid.Row="0" Margin="0, 10, 0, 0">
            <Grid.ColumnDefinitions>
                <ColumnDefinition Width="*" />
                <ColumnDefinition Width="10" />
                <ColumnDefinition Width="155" />
                <ColumnDefinition Width="10" />
                <ColumnDefinition Width="155" />
            </Grid.ColumnDefinitions>
            
            <Label Grid.Column="0" x:Name="lblPackagePath" 
               Foreground="Blue" Cursor="Hand" 
               FontSize="14" HorizontalAlignment="Right" 
               MouseDown="lblPackagePath_MouseDown">lblPackagePath</Label>
            <Button Grid.Column="2" x:Name="btnLocatePackage" 
               Click="btnLocatePackage_Click">Load SSIS Package</Button>
            <Button Grid.Column="4" x:Name="btnClearPackage" 
               Click="btnClearPackage_Click">Clear Package</Button>
        </Grid>
 
        <WindowsFormsHost Grid.Row="1" 
                 Name="WFHTBConnections" Margin="0, 15, 0, 0">
            <WF:DataGridView x:Name="TBConnections" 
                 BackgroundColor="White" 
                 AutoSizeColumnsMode="AllCells"></WF:DataGridView>
        </WindowsFormsHost>
 
        <WindowsFormsHost Grid.Row="2" 
                Name="WFHTBExecutionLog" Margin="0, 15, 0, 0">
            <WF:DataGridView x:Name="TBExecutionLog" 
                BackgroundColor="White" ReadOnly="True" 
                AutoSizeColumnsMode="AllCells"></WF:DataGridView>
        </WindowsFormsHost>
 
        <Grid Grid.Row="3" Margin="0, 10, 0, 0">
            <Grid.ColumnDefinitions>
                <ColumnDefinition Width="*" />
                <ColumnDefinition Width="300" />
                <ColumnDefinition Width="10" />
                <ColumnDefinition Width="180" />
                <ColumnDefinition Width="10" />
                <ColumnDefinition Width="180" />
            </Grid.ColumnDefinitions>
 
            <Button Grid.Column="1" x:Name="btnLaunchSSIS" 
               Click="btnLaunchSSIS_Click">Execute SSIS Package</Button>
            <Button Grid.Column="3" x:Name="btnClearExecutionLog" 
               Click="btnClearExecutionLog_Click">Clear SSIS Execution Log</Button>
            <Button Grid.Column="5" x:Name="btnClose" 
                Click="btnClose_Click">Close</Button>
        </Grid>
        
    </Grid>
</Window>

The code-behind file for this "XAML" file is the following:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Data;
using SSISWindowsLibrary;
using Microsoft.SqlServer.Dts.Runtime;
 
namespace SSISWindowsLauncher
{
    /// <summary>
    /// Interaction logic for WinMain.xaml
    /// </summary>
    public partial class WinMain : Window
    {
        private DTSPackage Pkg;
 
        public WinMain()
        {
            InitializeComponent();
 
            TBConnections.ColumnHeadersHeight = 30;
            TBConnections.ColumnHeadersDefaultCellStyle.BackColor = 
                          System.Drawing.Color.SeaShell;
            TBConnections.ColumnHeadersDefaultCellStyle.Font = 
                          new System.Drawing.Font("Verdana", 8, 
          	System.Drawing.FontStyle.Bold);
            TBConnections.CellBeginEdit += 
                 new System.Windows.Forms.DataGridViewCellCancelEventHandler(
                 TBConnections_CellBeginEdit);
            TBConnections.CellEndEdit += 
               new System.Windows.Forms.DataGridViewCellEventHandler(
               TBConnections_CellEndEdit);
 
            TBExecutionLog.ColumnHeadersHeight = 30;
            TBExecutionLog.ColumnHeadersDefaultCellStyle.BackColor = 
                           System.Drawing.Color.SeaShell;
            TBExecutionLog.DefaultCellStyle.WrapMode = 
                           System.Windows.Forms.DataGridViewTriState.True;
            TBExecutionLog.AutoSizeColumnsMode = 
          	System.Windows.Forms.DataGridViewAutoSizeColumnsMode.DisplayedCells;
            TBExecutionLog.ColumnHeadersDefaultCellStyle.Font = 
          	new System.Drawing.Font("Verdana", 8, System.Drawing.FontStyle.Bold);
            TBExecutionLog.RowsAdded += 
                new System.Windows.Forms.DataGridViewRowsAddedEventHandler(
                TBExecutionLog_RowsAdded);
 
            this.WindowStyle = WindowStyle.ThreeDBorderWindow;
            Pkg = new DTSPackage();
            UpdatelblPackagePath();
        }
 
        private void TBConnections_CellBeginEdit(object sender, 
          System.Windows.Forms.DataGridViewCellCancelEventArgs e)
        {
            System.Windows.Forms.DataGridViewCell Cell = 
          TBConnections.Rows[e.RowIndex].Cells[e.ColumnIndex];
            Cell.Style.Font = new System.Drawing.Font("Verdana", 8, 
          System.Drawing.FontStyle.Italic|System.Drawing.FontStyle.Bold);
        }
 
        private void TBConnections_CellEndEdit(object sender, 
          System.Windows.Forms.DataGridViewCellEventArgs e)
        {
            System.Windows.Forms.DataGridViewCell Cell = 
          TBConnections.Rows[e.RowIndex].Cells[e.ColumnIndex];
            Cell.Style.Font = new System.Drawing.Font("Verdana", 8, 
          System.Drawing.FontStyle.Regular);
        }
 
        private void TBExecutionLog_RowsAdded(Object sender, 
          System.Windows.Forms.DataGridViewRowsAddedEventArgs e)
        {
            if (TBExecutionLog.DataSource == null)
            {
                return;
            }
 
            foreach (System.Windows.Forms.DataGridViewRow Row in TBExecutionLog.Rows)
            {
                string ResultCode = Row.Cells[0].Value.ToString();
 
                System.Drawing.Color TextColor;
                if (ResultCode == "0")
                {
                    TextColor = System.Drawing.Color.Green;
                }
                else if (ResultCode == "1")
                {
                    TextColor = System.Drawing.Color.DarkOrange;
                }
                else
                {
                    TextColor = System.Drawing.Color.Red;
                }
 
                foreach (System.Windows.Forms.DataGridViewCell Cell in Row.Cells)
                {
                    Cell.Style.ForeColor = TextColor;
                }
            }
        }
 
        private void UpdatelblPackagePath()
        {
            string PkgLocation = Pkg.PkgLocation;
 
            if (PkgLocation == null)
            {
                lblPackagePath.Content = "Please select the SSIS Package to run";
            }
            else {
                lblPackagePath.Content = "SSIS Package to run: " + PkgLocation;
            }
        }
 
        private void btnLaunchSSIS_Click(object sender, RoutedEventArgs e)
        {
            SSISEventListener Listerner = new SSISEventListener();
 
            string PkgLocation = Pkg.PkgLocation;
            if (PkgLocation == null)
            {
                MessageBox.Show("Please locate the SSIS Package to run");
                return;
            }
 
            System.IO.FileInfo file = new System.IO.FileInfo(PkgLocation);
            if (!file.Exists)
            {
                MessageBox.Show("The package file does not exist, 
           	please make sure you have the SSIS package file in the right place.");
                return;
            }
 
            if (Pkg.PackageStatus != DTSPackageStatus.Loaded)
            {
                MessageBox.Show("The package file is not loaded successfully. 
           	Please check if the package file is a valid one.");
                return;
            }
 
            btnClearExecutionLog_Click(null, null);
            DispatcherHelper.DoEvents();
 
            System.Data.DataView aView = (System.Data.DataView) TBConnections.DataSource;
            System.Collections.Hashtable aHashTable = new System.Collections.Hashtable();
 
            for (int Idex = 0; Idex < aView.Count; Idex++)
            {
                aHashTable.Add(aView[Idex][0], aView[Idex][1]);
            }
 
            Pkg.SetPakageConnections(aHashTable);
 
            System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
            DTSExecResult PkgResult = Pkg.Execute(Listerner);
            System.Windows.Forms.Cursor.Current = null;
 
            int ResultCode = 0;
            if (PkgResult.ToString() != "Success")
            {
                ResultCode = 3;
            }
 
            int EventCount = Listerner.EventCount;
 
            string[] LogData = new string[4];
            LogData[0] = ResultCode.ToString();
            LogData[1] = (EventCount + 1).ToString();
            LogData[2] = System.DateTime.Now.ToLongTimeString();
            LogData[3] = PkgResult.ToString();
            Listerner.EventLogTable.Rows.Add(LogData);
 
            System.Data.DataView EventLogTableView = Listerner.EventLogTable.DefaultView;
            EventLogTableView.AllowNew = false;
            EventLogTableView.AllowDelete = false;
            EventLogTableView.AllowEdit = false;
 
            TBExecutionLog.DataSource = EventLogTableView;
            TBExecutionLog.Columns[0].Visible = false;
            TBExecutionLog.Columns[2].DefaultCellStyle.Format = 
                                      "MM/dd/yyyy hh:mm:ss tt";
 
        }
 
        private void btnLocatePackage_Click(object sender, RoutedEventArgs e)
        {
            Microsoft.Win32.OpenFileDialog dlg = new Microsoft.Win32.OpenFileDialog();
            dlg.DefaultExt = ".txt";
            dlg.Filter = "SSIS Package (.dtsx)|*.dtsx";
 
            string PkgLocation = null;
            Nullable<bool> result = dlg.ShowDialog();
            if (result != true)
            {
                return;
            }
 
            PkgLocation = dlg.FileName;
 
            try
            {
                Pkg.LoadPackage(PkgLocation);
            }
            catch (System.Exception EX)
            {
                MessageBox.Show("The is a problem to load the SSIS package, 
           	please make sure that it is a valid SSIS package file - " + 
           	System.DateTime.Now.ToString() + ".\n\n" + EX.ToString());
                btnClearPackage_Click(null, null);
                UpdatelblPackagePath();
                return;
            }
 
            if (Pkg.PackageStatus != DTSPackageStatus.Loaded) {
                MessageBox.Show("There is a problem to load the package. 
           	Please make sure the SSIS package is valid.");
                btnClearPackage_Click(null, null);
                UpdatelblPackagePath();
            }
 
            System.Collections.Hashtable PackageConnections = Pkg.GetPackageConnections();
            System.Data.DataTable PackageConnectionsTable = new System.Data.DataTable();
            PackageConnectionsTable.Columns.Add("SSIS Connection Name", 
          	System.Type.GetType("System.String"));
            PackageConnectionsTable.Columns.Add("Connection String", 
          	System.Type.GetType("System.String"));
 
            foreach (System.Collections.DictionaryEntry DE in PackageConnections)
            {
                string[] RowData = { (string)DE.Key, (string)DE.Value };
                PackageConnectionsTable.Rows.Add(RowData);
            }
 
            PackageConnectionsTable.Columns[0].ReadOnly = true;
            System.Data.DataView PackageConnectionTableView = 
          	PackageConnectionsTable.DefaultView;
            PackageConnectionTableView.AllowDelete = false;
            PackageConnectionTableView.AllowNew = false;
            TBConnections.DataSource = PackageConnectionTableView;
            TBExecutionLog.DataSource = null;
 
            UpdatelblPackagePath();
        }
 
        private void btnClearPackage_Click(object sender, RoutedEventArgs e)
        {
            Pkg.ClearPackage();
            UpdatelblPackagePath();
            TBConnections.DataSource = null;
            TBExecutionLog.DataSource = null;
        }
 
        private void btnClose_Click(object sender, RoutedEventArgs e)
        {
            this.Close();
        }
 
        private void lblPackagePath_MouseDown(object sender, MouseButtonEventArgs e)
        {
            btnLocatePackage_Click(null, null);
        }
 
        private void btnClearExecutionLog_Click(object sender, RoutedEventArgs e)
        {
            TBExecutionLog.DataSource = null;
        }
    }
}

The SSIS Package

In order to test the WPF application, I created a simple SSIS package. I chose SQL Server 2008 as my test platform to build the SSIS package. Before I build the SSIS package, I run the following SQL query to create two tables [SSISExperiment] and [SSISExperimentTarget] and to insert some data into the table [SSISExperiment].

-- Setup the tables needed for the SSIS package "SampleSSIS"
-- Create two tables [SSISExperiment] and [SSISExperimentTarget]
-- Insert some data into the table [SSISExperiment]
 
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = 
            OBJECT_ID(N'[dbo].[SSISExperiment]') AND type in (N'U'))
DROP TABLE [dbo].[SSISExperiment]
 
CREATE TABLE [dbo].[SSISExperiment](
 [ID] [int] NOT NULL,
 [Name] [varchar](50) NOT NULL,
 [Time] [datetime] NOT NULL
) ON [PRIMARY]
 
INSERT INTO [SSISExperiment] VALUES(1, 'Name 1', GETDATE())
INSERT INTO [SSISExperiment] VALUES(2, 'Name 2', GETDATE())
INSERT INTO [SSISExperiment] VALUES(3, 'Name 3', GETDATE())
INSERT INTO [SSISExperiment] VALUES(4, 'Name 4', GETDATE())
 
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = 
            OBJECT_ID(N'[dbo].[SSISExperimentTarget]') AND type in (N'U'))
DROP TABLE [dbo].[SSISExperimentTarget]
 
CREATE TABLE [dbo].[SSISExperimentTarget](
 [ID] [int] NOT NULL,
 [Name] [varchar](50) NOT NULL,
 [Time] [datetime] NOT NULL
) ON [PRIMARY]
 
GO

The sample SSIS package will be transferring the data from [SSISExperiment] to [SSISExperimentTarget]. The following picture shows the data flow mapping of the SSIS package:

Run the Application

Compile the entire Visual Studio solution, which includes the Class Library, the WPF application, and the SSIS package. We can then launch the WPF application "SSISWindowsLauncher" either in Debug mode or by double clicking the compiled EXE. We can then click the "Load SSIS Package" button on the WPF application to browse to the "bin" folder of the "SampleSSIS" project and select the "SamplePackage.dtsx" file that we just created.

Click the "Open" button, we can then load the SSIS package.

After the SSIS package is successfully loaded, the application shows us the connection strings for the connections used in the SSIS package. In our "SamplePackage.dtsx", we have only one connection. If you click on the connection string text, you will find that the application allows you to change the connection string. In this simple test, let me not make any change to the connection string. But if you play with this WPF application a little more, you may find some other nice features in supporting loading and executing SSIS packages.

Click the "Execute SSIS Package" button; the application will start to execute the loaded SSIS package. The application shows the status of some of the important steps during the execution. In this case, the SSIS package is executed successfully.

Now we can go to SQL Server and take a look at the table [SSISExperimentTarget], and the data is indeed transferred successfully.

Points of Interest

• The article introduced a method to load and execute SSIS packages in a Microsoft Windows application. The sample WPF application that comes with this article is no where close to a commercial grade software application. But it has pretty much all the basic functionalities to load and execute an SSIS package in Microsoft Windows. If you want, you can use it as it is, or make any changes to better fit your needs.
• If you want to compile and test this application yourself, and if you want to use my sample SSIS package, you will need to have SQL Server and you will need to have the required permission to it. In my case, I am the server administrator of my SQL Server. You will need to run the SQL script that comes with this article before you run the SSIS package, and you will need to change the server name and database name in the connection "Experiment" in "SamplePackage.dtsx". If you have expertise with database connection strings, you may be able to change the SQL Server name and the database name after the SSIS package is loaded in the "SSISWindowsLauncher" application.
• In the WPF application, you may notice that I used two "WindowsFormsHost" containers to insert two "DataGridView" controls designed for Windows Forms applications into the WPF application. It is just a matter of personal preference, simply because I like the font shown in the "good old days" controls. WPF applications will need .NET Framework to run anyway, so mixing Windows Forms controls in WPF applications should not be cause deployment and maintenance problems.
• I tested many SSIS packages with this application, and I noticed that SSIS packages do not do lazy evaluation. This means that all the execution steps will be independently evaluated before the package execution starts. Although it is a safe approach, it limits developers from creating more powerful SSIS packages.
• Another thing to remind my readers is that you may have noticed that I can view your entire connection string for all the connections in the SSIS packages including the user name and the password. So it is a better idea to use integrated authentication than SQL Server authentication, unless you can find a good way to encrypt your user name and password.
• The application that comes with this article definitely leaves a lot of room for improvement, particularly the class "SSISEventListener". If you want more detailed SSIS execution information and if you want the information to be real time, you can create your own listener and use it appropriately.
• By looking into the application that comes with this article, you will have a better insight into the SSIS packages and better understanding of the SSIS "technology".

History

This is the first revision of the article.