Load and Execute SSIS Packages in Microsoft Windows

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;
        }

    }
}