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:
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.
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!
