Queue up BackgroundWorkers OR wait for BackgroundWorker to finish without blocking the UI

This is a new one, so thought I'd put it here.
I have a procedure that uses a BackgroundWorker and updates a log window in my main form. Simple stuff.
I use this procedure for more than one task, incorporating more than one procedure. In the case I solved here, I had to wait for one BackgroundWorker to complete before moving on with the code in the Main Thread. I couldn't trigger it in the RunWorkerComplete event, as the BackgroundWorker was used multiple times throughout the application.
So here it is: (I used ThreadPool and Invoked the actions!)

private void Button1_Click(object sender, EventArgs e)
        {
            ThreadPool.QueueUserWorkItem(delegate
            {
                Invoke(new Action(delegate { ProcedureWithBackgroundWorker(); }));

                while (BW.IsBusy)
                {
                    Thread.Sleep(100);
                }

                Invoke(new Action(delegate { SecondProcedureThatWaitsForTheOneAboveToFinish(); }));
            });
        }

Create and export Excel in C# Winform application

So I used to do this with something along the lines of:

using System;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

namespace WindowsApplication1
{
 public partial class Form1 : Form
 { 
    public Form1()
  {
   InitializeComponent();
  }
 private void button1_Click(object sender, EventArgs e)
 {
  Excel.Application excel = new Excel.Application();
  Excel.Workbook wb = new Excel.Workbook();
  excel.Workbook.Add(wb);

etc...etc...etc..

Then I would loop through, adding data to a worksheet. This was very memory intensive and very SLOW....
This is how I do it now.

I create an Excel file using Excel - I add and name worksheets, I format columns etc and get it to 'look' how I want it to.

I then add this to the application in a folder called 'Excel Templates'. You can then change the deployment properties to export and create this file on installation.

The idea is that the application will copy, rename and save this file. It then creates an OLEDB connection and adds rows using an update command (I use update because it keeps the cell formatting and doesn't append after 'blank rows').

This is a basic class for copying and populating the Excel template. The 'FileName' parameter is what and where you want to save the file as.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace SpaceApp_v0._1.Classes
{
    class ExcelClassExample
    {
        static DataSet ds_Excel;

        public void CreateViewer(string FileName)
        {
            string myPath = System.Windows.Forms.Application.StartupPath + @"\Excel Templates\ExcelTemplate.xls";

            File.Copy(myPath, FileName, true);

            Populate_Excel(FileName);
        }

        private void Populate_Excel(string FileName)
        {
            SqlConnection conn = new SqlConnection(ConnectionsClass.Server);

            string sqlQuery = "select FieldNames from TableName Where parameters = 'Value'";

            SqlCommand cmd = new SqlCommand(sqlQuery, conn);

            ds_Excel = new DataSet();

            SqlDataAdapter da = new SqlDataAdapter(cmd);

            conn.Open();

            da.Fill(ds_Excel, "TableName");

            conn.Close();

            string DSN = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=No;IMEX=2\"", FileName);
            using (System.Data.OleDb.OleDbConnection Con = new System.Data.OleDb.OleDbConnection(DSN))
            {

                Con.Open();

                using (System.Data.OleDb.OleDbCommand Com = new System.Data.OleDb.OleDbCommand())
                {
                    int Row = 1;
                    foreach (DataRow row in ds_Excel.Tables["TableName"].Rows)
                    {
                        Com.Connection = Con;
                        Com.CommandText = string.Format("UPDATE [Sheet1$A" + Row + ":Z" + Row + "] SET F1 = '{0}'", row["Column1"]);

                        Com.ExecuteNonQuery();
                        Row++;
                    }
                }
                Con.Close();
            }
        }

    }
}

You can call this class from your main app using a button click event. I have used this is a background worker, creating 2,500 Excel 'templates' in a short period of time, with progress bar etc.

Embed Console Window in Winform Application

This has been bugging me for a while now.

I use TelNet to pass commands to a Unix box.  I wanted to embed the TelNet interface into a Winform application.

I concentrated on embedding a console app within the winform app. WRONG.

I used TelNet libraries and messed around looking to use RichTextBoxes. WRONG.
Well. Probably not that wrong, they mostly worked but weren't very good.

Here is what I have done:

Process TelNet = new Process();

TelNet.StartInfo.FileName = "TelNet.exe";

TelNet.Start();

If you need any more clarification please leave.

This prevents cross-thrreading issues, launching new applications and all sorts of problems.

It works for me.  I know it's simple.  It's all I require.

Run an SSIS package on a remote server without SQL Agent

Situation: no SQL Agent, need to run SSIS package on a server remotely via a button-click event in a winforms app (C#).

This solution will run an SSIS package on a remote server while showing the progress in a multi-line textbox.  To do this effectively we will need to use a BackgroundWorker.

I used an executable called PsExec that is freely available to download.

To set up the logging, right click in your package ControlFlow and set up the logging to a SQL Server table.  This by default will be dbo.sysdtslog90 (for 2005 anyway; for 2008 it will be dbo.sysdtslog100 etc.).

PsExec allows for running command line routines on a remote server.  In this case I am calling a package through a command line routine using DtExec.  If you are unsure on DtExec please google it, I may do another post on it but it is pretty simple stuff and easy to work out.  PsExec simply allows you to pass a computer name that you would like to run the process on.

I start the PsExec, give it a computer name and pass in the Command Line routine to run a SSIS package.

The Background Worker then loops through the log table displaying the log results as the package runs.  Once an 'End of Package' log has been recorded the Background Worker stops. Simples.

If PsExec fails, or DtExec fails, you will also get an error message returned.  I was getting error code 128 for a while - it turned out the DBAs where remote desktoping on to the server and then not logging off, so there were too many users logged in.  Once I got them to log out all worked well.

private void RunSSISPackage()
        {
            try
            {
                SqlConnection conn = new SqlConnection(ConnectionsClass.Server);
                conn.Open();

                string TruncateSQL = "truncate table dbo.sysdtslog90";

                SqlCommand TruncateCMD = new SqlCommand(TruncateSQL, conn);

                TruncateCMD.ExecuteNonQuery();

                conn.Close();

                Process process = new Process();
                process.StartInfo.FileName = @"C:\Documents and Settings\Geoff.Parsons\My Documents\PsExec.exe";
                process.StartInfo.Arguments = "\\\\ServerName -d \"C:\\dtexec.exe\" /SQL PackageName /SERVER ServerName";
                process.StartInfo.UseShellExecute = false;
                process.StartInfo.CreateNoWindow = true;
                process.StartInfo.RedirectStandardOutput = true;
                process.StartInfo.RedirectStandardError = true;

                process.Start();
                process.WaitForExit();

                string strOutput = process.StandardOutput.ReadToEnd();
                string errOutput = process.StandardError.ReadToEnd();


                if (!process.HasExited)
                {
                    process.Kill();
                }

                PackageResultTB.AppendText(strOutput);
                PackageResultTB.AppendText(errOutput);

                if (errOutput != "")
                {
                    if (!errOutput.ToString().Contains("dtexec.exe started on SERVERNAME with process ID"))
                    {
                        return;
                    }
                }

                BW = new BackgroundWorker();
                BW.WorkerReportsProgress = true;
                BW.WorkerSupportsCancellation = false;
                BW.DoWork += new DoWorkEventHandler(BW_DoWork);
                BW.ProgressChanged += new ProgressChangedEventHandler(BW_ProgressChanged);
                BW.RunWorkerCompleted += new RunWorkerCompletedEventHandler(BW_RunWorkerCompleted);

                PrepareControlsForThreading(false);

                BW.RunWorkerAsync();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return;
            }
        }

This DoWork event loops through a select query to return new rows. I added a column to sysdtslog90 called 'Logged' which I populated each time a row was read to prevent repeating lines from the log table. On the front-end of the application I have a multi-line textbox called PackageResultTB.

private void BW_DoWork(object sender, DoWorkEventArgs e)
        {
            BW.ReportProgress(0, "Loading Package...");

            SqlConnection conn = new SqlConnection(ConnectionsClass.Server);
            conn.Open();

            bool Continue = true;
            while (Continue)
            {
                string SqlQuery = "select id,event,source,message,datacode, Logged from dbo.sysdtslog90 order by id";
                
                SqlCommand cmd = new SqlCommand(SqlQuery, conn);
                da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                dt.PrimaryKey = new DataColumn[] { dt.Columns["id"] };

                string Update = "";
                

                foreach (DataRow row in dt.Rows)
                {
                    if (row["message"].ToString().Contains("End of package"))
                    {
                        Continue = false;
                    }
                    if (row["Logged"].ToString() == "")
                    {
                        BW.ReportProgress(0, "\r\n" + row["event"].ToString() + "\r\n        " + row["source"].ToString() + "\r\n        " + row["message"].ToString() + " - " + row["datacode"].ToString() + "%\r\n");
                        Update = string.Format("Update dbo.sysdtslog90 set Logged = 'TRUE' where id = '{0}'", row["id"].ToString());
                        UpCmd = new SqlCommand(Update, conn);
                        UpCmd.ExecuteNonQuery();
                    }

                }
                Thread.Sleep(1000);
            }
            conn.Close();
        }


private void BW_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            PackageResultTB.AppendText(e.UserState.ToString());
        }


private void BW_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            try
            {
                //Catch any errors
                if (e.Error != null)
                {
                    MessageBox.Show(e.Error.Message);
                    return;
                }
                else
                {
                    return;
                }
            }
            catch (SystemException ex)
            {
                MessageBox.Show(ex.Message);
                return;
            }
        }

Creating SSIS package from C# application

Unusual one this.  Had a need to truncate about 70 Raw Files after a large ETL process.  There is no specific task in SSIS, but you can set a DataFlow up that passes no rows to the destination and this will sort the problem out. 

Rather than making 70+ DataFlows I initially used a loop, go through each raw file and truncate it - but this changed the meta-data each time (as RawFiles don't use connections) and caused a lot of validation errors.  I could have delayed validation on the tasks, but this would have taken a very long time to go through 10+ packages looking for the use of the raw files.

Instead I chose to create a class in C# that would loop through all the RawFiles in a folder and create a bespoke DataFlow for them within a new package.

The code creates a new package, then uses a class to return a new DataFlow task in a loop.

The DataFlow consists of a RawFile Source followed by a Conditional Split and a RawFile Destination.
The conditional split creates an output with the condition "1 == 0"; as 1 will never equal 0 it will never pass a row so will truncate the RawFile without affecting the meta-data.

This is my class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using wrap = Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using System.Xml;
using System.Data.Common;
using System.Configuration;
using System.Collections;
using System.IO;

namespace SpaceApp_v0._1.Classes
{
    public class RawFileCleanUp
    {

        public TaskHost SSISPackage(Package package, Executable dataFlowTask, string DataFlowTaskName, string RawFileLocation)
        {
            //// Add DataFlow
            TaskHost taskHost = dataFlowTask as TaskHost;
            taskHost.Name = DataFlowTaskName;

            MainPipe pipeline = taskHost.InnerObject as MainPipe;

            //// Add RawFile Source
            IDTSComponentMetaData90 rawSource = pipeline.ComponentMetaDataCollection.New();
            rawSource.ComponentClassID = "DTSAdapter.RawSource.1";
            rawSource.ValidateExternalMetadata = true;
            IDTSDesigntimeComponent90 rawSourceDesignTimeComponent = rawSource.Instantiate();
            rawSourceDesignTimeComponent.ProvideComponentProperties();
            rawSource.Name = "RawFile Source";

            rawSourceDesignTimeComponent.SetComponentProperty("AccessMode", 0);
            rawSourceDesignTimeComponent.SetComponentProperty("FileName", RawFileLocation);

            rawSourceDesignTimeComponent.AcquireConnections(null);
            rawSourceDesignTimeComponent.ReinitializeMetaData();
            rawSourceDesignTimeComponent.ReleaseConnections();
            

            //// Add Conditional Split to drop ALL rows
            IDTSComponentMetaData90 conSplit = pipeline.ComponentMetaDataCollection.New();
            conSplit.ComponentClassID = "DTSTransform.ConditionalSplit";
            conSplit.Name = "Conditional Split";
            conSplit.Description = "Conditional Split Transform";

            CManagedComponentWrapper splitWrapper = conSplit.Instantiate();
            splitWrapper.ProvideComponentProperties();
            splitWrapper.AcquireConnections(null);
            splitWrapper.ReinitializeMetaData();
            splitWrapper.ReleaseConnections();
            

            //// Connect RawFile Source to Split
            IDTSPath90 path = pipeline.PathCollection.New();
            path.AttachPathAndPropagateNotifications(rawSource.OutputCollection[0], conSplit.InputCollection[0]);

            //// Configure split
            IDTSOutput90 splitOutput = splitWrapper.InsertOutput(DTSInsertPlacement.IP_BEFORE, conSplit.OutputCollection[0].ID);
            splitOutput.Name = "Split Output 1";
            splitOutput.Description = "Passes zero rows out";
            splitOutput.IsErrorOut = false;

            IDTSInput90 splitInput = conSplit.InputCollection[0];
            IDTSInputColumnCollection90 splitInputColumns = splitInput.InputColumnCollection;
            IDTSVirtualInput90 splitVirtualInput = splitInput.GetVirtualInput();
            IDTSVirtualInputColumnCollection90 splitVirtualInputColumns = splitVirtualInput.VirtualInputColumnCollection;

            string ConColumn = "";
            int columnCount = splitVirtualInputColumns.Count;
            for (int i = 0; i < columnCount; i++)
            {
                splitWrapper.SetUsageType(splitInput.ID,splitVirtualInput,splitVirtualInputColumns[i].LineageID,DTSUsageType.UT_READONLY);
                if (i == 0)
                {
                    ConColumn = splitVirtualInputColumns[i].Name;
                }
            }

            splitWrapper.SetOutputProperty(splitOutput.ID, "EvaluationOrder",0);
            splitWrapper.SetOutputProperty(splitOutput.ID, "FriendlyExpression", "1 == 0");


            //// Add RawFile Destination
            IDTSComponentMetaData90 rawDestination = pipeline.ComponentMetaDataCollection.New();
            rawDestination.ComponentClassID = "DTSAdapter.RawDestination.1";
            rawDestination.ValidateExternalMetadata = true;
            IDTSDesigntimeComponent90 rawDestinationDeignTimeComponent = rawDestination.Instantiate();
            rawDestinationDeignTimeComponent.ProvideComponentProperties();
            rawDestination.Name = "RawFile Destination";

            rawDestinationDeignTimeComponent.SetComponentProperty("AccessMode", 0);
            rawDestinationDeignTimeComponent.SetComponentProperty("FileName", RawFileLocation);

            rawDestinationDeignTimeComponent.AcquireConnections(null);
            rawDestinationDeignTimeComponent.ReinitializeMetaData();
            rawDestinationDeignTimeComponent.ReleaseConnections();


            IDTSPath90 DestPath = pipeline.PathCollection.New();
            DestPath.AttachPathAndPropagateNotifications(conSplit.OutputCollection[0], rawDestination.InputCollection[0]);

            //// Connect Split to Destination
            IDTSInput90 rawInput = rawDestination.InputCollection[0];
            IDTSVirtualInput90 rawVInput = rawInput.GetVirtualInput();

            foreach (IDTSVirtualInputColumn90 vColumn in rawVInput.VirtualInputColumnCollection)
            {
                rawDestinationDeignTimeComponent.SetUsageType(rawInput.ID, rawVInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
            }

            ////Repeat connections and validate to ensure package will run first time with no warnings and proper metadata.
            rawSourceDesignTimeComponent.AcquireConnections(null);
            rawSourceDesignTimeComponent.ReinitializeMetaData();
            rawSourceDesignTimeComponent.ReleaseConnections();

            splitWrapper.AcquireConnections(null);
            splitWrapper.ReinitializeMetaData();
            splitWrapper.ReleaseConnections();
            
            rawDestinationDeignTimeComponent.AcquireConnections(null);
            rawDestinationDeignTimeComponent.ReinitializeMetaData();
            rawDestinationDeignTimeComponent.ReleaseConnections();

            rawSourceDesignTimeComponent.Validate();
            splitWrapper.Validate();
            rawDestinationDeignTimeComponent.Validate();


            return taskHost;



        }

        


    }

}

You may notice that I call the AcquireConnections, ReinitialiseMetaData and ReleaseConnections more than once.  This seem to relieve an issue I had with validation warnings after the package was saved.  With the code as it is you can run the package the instant it has been created.

This is the code to call the class:

You pick the folder that holds the RawFiles and let it run.  I would recommend changing the NewFileName or setting it to a variable like a text box, or even the original FolderBrowserDialog plus a new file name.

string Folder = "";

            FolderBrowserDialog folderBrowser = new FolderBrowserDialog();
            folderBrowser.RootFolder = Environment.SpecialFolder.Desktop;

            DialogResult result = folderBrowser.ShowDialog();
            if (result == DialogResult.OK)
            {
                Folder = folderBrowser.SelectedPath;
            }

            string[] files = Directory.GetFiles(Folder);

            Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();

            Package package = new Package();

            string RawFile = "";

            foreach (string file in files)
            {
                RawFile = file.Substring(Folder.Length, file.Length - Folder.Length);
                RawFile = RawFile.Replace("\\", "");

                RawFileCleanUp rawFileClean = new RawFileCleanUp();

                Executable dataFlowTask = package.Executables.Add("STOCK:PipelineTask");

                TaskHost taskHost = rawFileClean.SSISPackage(package, dataFlowTask, RawFile, file);


            }

            package.Validate(null, null, null, null);

            string NewFileName = Folder + "\\RawFileCleanUp.dtsx";

            XmlDocument myPkgDocument = new XmlDocument();
            package.SaveToXML(ref myPkgDocument, null, null);

            app.SaveToXml(NewFileName, package, null);