SSIS – Custom Control Flow Component – Execute SQL Job And Wait

Sometimes you have some pretty complex ETL’s going in SSIS, and you might have multiple projects/solutions that need to call other SSIS Packages or SQL Agent Jobs and you have a pretty big production going on. You might have an ETL solution that needs to kick off other packages, and you can either import those into your solution or call them where they lie on the file system/SQL server, etc. You might have to call some SQL agent jobs, and most often they are async calls (you dont need to wait for them to come back) and this works nicely, I do this all the time. The Execute SQL Agent Task in SSIS works nice, or you can just call the SQL statement to execute a job, either way, it kicks off the job and then just comes back successful right away, and doesn’t care if the job actually succeeds. You might want this in some scenarios, and the built in functionality works great.

But what if you want to just call an existing SQL Agent job and actually wait for the job to finish (success or failure)? There isn’t anything that I could see built in to SSIS to do this, sp_start_job is asynchronous, so you are out of luck there. I figured I could call sp_start_job, then create a for loop in SSIS and just check the status every X seconds/minutes, but I would have to either make this a package I could use everywhere or reproduce the same logic in multiple solutions, so I shied away from that solution.

What I decided to do was build a custom SSIS control flow task in .NET that will execute a SQL agent job and check the status and wait until it finishes. A disclaimer: This is going to be a lot of code :) also, it could be improved (but what couldn’t?) – this was a 1.5-2 hour experiment.

First, I created a VS2008 C# class library. I tried adding a UI to my task, but I couldn’t get it working so there is some code there for that but it’s commented out.

here is what my solution looks like:

Capture

import the correct namespaces:

 
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using System.Net;
using System.Net.NetworkInformation;
using System.Xml;
using Microsoft.SqlServer.Dts.Runtime.Design;
using System.Data.SqlClient;

Next, you need to create the actual skeleton/wrapper for your component. You can see I have two properties, job name, server name. It could be expanded to have the connection string or use an existing connection in SSIS, I wasn’t that ambitious. The “Execute” method basically just calls some functions and waits for result.

 
namespace ExecuteSQLJobAndWaitControlTask
{
 
    [DtsTask(
        Description = "Execute SQL Job And Wait", 
        DisplayName = "Execute SQL Job And Wait", 
        TaskContact = "Steve Novoselac",
        TaskType = "SSIS Helper Task",
        RequiredProductLevel = DTSProductLevel.None)]
    public class ExecuteSQLJobAndWaitControlTask : Task, IDTSComponentPersist
 
    {
        private string _jobName;
        private string _serverName;
 
        /// <summary>
        /// The sql job name
        /// </summary>
        public string JobName
        {
            get { return _jobName; }
            set { _jobName = value; }
        }
 
        /// <summary>
        /// The sql server name
        /// </summary>
        public string ServerName
        {
            get { return _serverName; }
            set { _serverName = value; }
        }
 
        public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
        {
            try
            {
 
                StartJob();
 
                System.Threading.Thread.Sleep(5000);
 
                do
                {
                    System.Threading.Thread.Sleep(5000);
 
 
                } while (IsJobRunning());
 
                if (DidJobSucceed())
                {
                    return DTSExecResult.Success;
                }
                else
                {
                    return DTSExecResult.Failure;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return DTSExecResult.Failure;
            }
        }
 
        public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
        {
 
            if (string.IsNullOrEmpty(_serverName) || string.IsNullOrEmpty(_jobName))
            {
                componentEvents.FireError(0, "You must specify a JobName and ServerName in the properties", "", "", 0);
                return DTSExecResult.Failure;
            }
            else
            {
                return DTSExecResult.Success;
            }
 
        }
 
        void IDTSComponentPersist.LoadFromXML(System.Xml.XmlElement node, IDTSInfoEvents infoEvents)
        {
            if (node.Name != "ExecuteSQLJobAndWaitTask")
            {
                throw new Exception(string.Format("Unexpected task element when loading task - {0}.", "ExecuteSQLJobAndWaitTask"));
            }
            else
            {
                this._jobName = node.Attributes.GetNamedItem("JobName").Value;
                this._serverName = node.Attributes.GetNamedItem("ServerName").Value;
            }
        }
 
        void IDTSComponentPersist.SaveToXML(System.Xml.XmlDocument doc, IDTSInfoEvents infoEvents)
        {
 
            XmlElement taskElement = doc.CreateElement(string.Empty, "ExecuteSQLJobAndWaitTask", string.Empty);
 
            XmlAttribute jobNameAttribute = doc.CreateAttribute(string.Empty, "JobName", string.Empty);
            jobNameAttribute.Value = this._jobName.ToString();
            taskElement.Attributes.Append(jobNameAttribute);
 
 
            XmlAttribute serverNameAttribute = doc.CreateAttribute(string.Empty, "ServerName", string.Empty);
            serverNameAttribute.Value = this._serverName.ToString();
            taskElement.Attributes.Append(serverNameAttribute);
 
            doc.AppendChild(taskElement);
        }

And then I have some helper methods, this is where the meat and potatoes are for this task. Now of course I could have the connection string once, etc. Like I said, it was a quick thing :). The heart of it is though, starting the job, checking if it is still running, and then after, if it succeeded. Pretty simple.

 
 
        private bool DidJobSucceed()
        {
            SqlConnection dbConn = new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=msdb;Data Source=" + ServerName);
            SqlCommand dbCmd = new SqlCommand("exec msdb.dbo.sp_help_job @job_name = N'" + JobName + "' ;", dbConn);
            dbConn.Open();
 
            SqlDataReader dr = dbCmd.ExecuteReader();
            dr.Read();
            int status = Convert.ToInt32(dr["last_run_outcome"]);
            dr.Close();
 
            dbConn.Close();
 
            if (status == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
 
        private bool IsJobRunning()
        {
 
            SqlConnection dbConn = new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=msdb;Data Source=" + ServerName);
            SqlCommand dbCmd = new SqlCommand("exec msdb.dbo.sp_help_job @job_name = N'" + JobName + "' ;", dbConn);
            dbConn.Open();
 
            SqlDataReader dr = dbCmd.ExecuteReader();
            dr.Read();
            int status = Convert.ToInt32(dr["current_execution_status"]);
            dr.Close();
 
            dbConn.Close();
 
            if (status == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
 
        }
 
        private void StartJob()
        {
            SqlConnection dbConn = new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=msdb;Data Source=" + ServerName);
            SqlCommand dbCmd = new SqlCommand("EXEC dbo.sp_start_job N'" + JobName + "' ;", dbConn);
            dbConn.Open();
            dbCmd.ExecuteNonQuery();
            dbConn.Close();
        }
   }

Now, to install this you need to register it in the GAC (global assembly cache), and then copy to the DTS/Tasks folder. Depending if you have VS2005 or VS2008 (or both) your gacutil path might be different.

cd\
c:
cd C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin
gacutil /uf "ExecuteSQLJobAndWaitTask"
gacutil /if "C:\Projects\SSISCustomTasks\ExecuteSQLJobAndWait\bin\Debug\ExecuteSQLJobAndWaitTask.dll"
copy "C:\Projects\SSISCustomTasks\ExecuteSQLJobAndWait\bin\Debug\ExecuteSQLJobAndWaitTask.dll" "C:\Program Files\Microsoft SQL Server\90\DTS\Tasks"

I have found once you have done that, you need to actually restart your SSIS service to make it work, but then you can use it in new Visual Studio SSIS packages.

Capture

Once you drag it on your package, you can set the JobName and ServerName property (from the properties window – remember, no GUI). and it should run.

Some notes:

If you kill the job, the SSIS task will fail (obviously). If you kill the SSIS package, the job will keep running. Maybe a future enhancement will be to capture the SSIS package fail/cancel and kill the job. Maybe :)

Attached is the source code for the task (Vs2008 C#) http://stevienova.com/ExecuteSQLJobAndWait.rar

This has been testing with BIDS VS2005. I take no responsibility if this blows up your system, computer, server, the world, etc.

Happy ETL’ing!



  • http://toddmcdermid.blogspot.com/ Todd McDermid

    Great idea! I'm going to post a link up at my custom objects directory – the SSIS Community Tasks and Components project on CodePlex (http://ssisctc.codeplex.com/). You should consider posting the code and binary up on CodePlex…

    A few notes from someone who's made quite a few custom tasks:
    1. You don't need to restart the SSIS service to use your new task in a project. After you compile, GAC, and copy your DLL to the Tasks folder, you just need to open a fresh instance of BIDS (you don't even need to close your custom-task-coding instance of VS).
    2. Since your properties are “simple types” (strings) – you don't need to use LoadFromXML/SaveToXML. SSIS will automagically pick up those properties, save and restore them for you.
    3. You could use an SSIS Connection Manager instead of passing in the server name only. It is a tad harder to code (reading through the connections collection and casting), but it does keep all your “connection” info in one place. (You can even restrict the Connection Manager type to SQL-only connections if you wish.)
    4. Adding a GUI is dead easy. Walkthough here: http://toddmcdermid.blogspot.com/2009/06/conver

    But this is a great idea… it's given me one or two ideas for some other useful Tasks.

  • http://toddmcdermid.blogspot.com/ Todd McDermid

    Great idea! I'm going to post a link up at my custom objects directory – the SSIS Community Tasks and Components project on CodePlex (http://ssisctc.codeplex.com/). You should consider posting the code and binary up on CodePlex…

    A few notes from someone who's made quite a few custom tasks:
    1. You don't need to restart the SSIS service to use your new task in a project. After you compile, GAC, and copy your DLL to the Tasks folder, you just need to open a fresh instance of BIDS (you don't even need to close your custom-task-coding instance of VS).
    2. Since your properties are “simple types” (strings) – you don't need to use LoadFromXML/SaveToXML. SSIS will automagically pick up those properties, save and restore them for you.
    3. You could use an SSIS Connection Manager instead of passing in the server name only. It is a tad harder to code (reading through the connections collection and casting), but it does keep all your “connection” info in one place. (You can even restrict the Connection Manager type to SQL-only connections if you wish.)
    4. Adding a GUI is dead easy. Walkthough here: http://toddmcdermid.blogspot.com/2009/06/conver

    But this is a great idea… it's given me one or two ideas for some other useful Tasks.

  • http://blog.stevienova.com Steve

    Hey Todd, cool, thanks for the tips, this was my first custom one so I kind of hobbled through it. I will post the code up to codeplex as well when I get a chance..

    thanks!

    Steve

  • http://blog.stevienova.com Steve

    Hey Todd, cool, thanks for the tips, this was my first custom one so I kind of hobbled through it. I will post the code up to codeplex as well when I get a chance..

    thanks!

    Steve

  • http://blog.stevienova.com Steve

    Hey Todd, cool, thanks for the tips, this was my first custom one so I kind
    of hobbled through it. I will post the code up to codeplex as well when I
    get a chance..

    thanks!

    Steve

  • http://blog.stevienova.com Steve

    Hey Todd, cool, thanks for the tips, this was my first custom one so I kind
    of hobbled through it. I will post the code up to codeplex as well when I
    get a chance..

    thanks!

    Steve

  • http://twitter.com/unruledboy ????

    why not accept a connection as a parameter, so that we don't have to specify the server name and a fixed initial catalog

  • Pingback: Script Component in SSIS « thulasirangan