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;

        /// 
        /// The sql job name
        /// 
        public string JobName
        {
            get { return _jobName; }
            set { _jobName = value; }
        }

        /// 
        /// The sql server name
        /// 
        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 FilesMicrosoft SDKsWindowsv6.0Abin
gacutil /uf "ExecuteSQLJobAndWaitTask"
gacutil /if "C:ProjectsSSISCustomTasksExecuteSQLJobAndWaitbinDebugExecuteSQLJobAndWaitTask.dll"
copy "C:ProjectsSSISCustomTasksExecuteSQLJobAndWaitbinDebugExecuteSQLJobAndWaitTask.dll" "C:Program FilesMicrosoft SQL Server90DTSTasks"

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!

About these ads

8 thoughts on “SSIS – Custom Control Flow Component – Execute SQL Job And Wait”

  1. 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.

    Like

    1. 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

      Like

    2. Hey Todd, cool, thanks for the tips, this was my first custom one so I kindof hobbled through it. I will post the code up to codeplex as well when Iget a chance..thanks!Steve

      Like

  2. 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.

    Like

  3. 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

    Like

  4. Hey Todd, cool, thanks for the tips, this was my first custom one so I kindof hobbled through it. I will post the code up to codeplex as well when Iget a chance..thanks!Steve

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s