SQLXAgent - Jobs for SQL Express - Part 5 of 6
Create and run jobs kinda like SQL Server Enterprise - How Packages Are Run
Part 1 - Using SQLXAgent
Part 2 - Architecture and Design Decisions
Part 3 - The CSV and Excel Importer Code
Part 4 - Job Scheduling Code
Part 5 - How Packages Are Run (this article)
Part 6 - Interesting Coding
Introduction
This article is the 5th part of a larger article series describing the SQLXAgent utility. In this article, I'll be describing the code used to run SQLXAgent packages.
An important point to keep in mind is that a SQLXAgent package has no reasonable resemblance to how you (may) know a DTSX package. The closest analog is to say that all SQLXAgent packages contain no more than a single-task, and that task is a script task (because you have to write code). Packages can be created using any .Net language that you might prefer, as long as they compile to a .PKG assembly (which is nothing more than a DLL with the "PKG" extension.
NOTE: Code snippets presented in the article may or may not reflect the absolutely latest and greatest version of the code. In the event that it does not exactly match the actual code, it will fairly close. Such is the nature of noticing issues while writing an article.
How Packages are Run
Packages are run via the SQLXPkgRunner command line application. Everything starts in the JobThread.ExecuteJob method, where the application is invoked. Irrelevant code has been omitted from the snippet below, but is otherwise discussed in Part 4 of this article series.
/// <summary>
/// Executes all of the steps for the job in (step.Position) sequential order.
/// </summary>
public virtual void ExecuteJob()
{
if (!this.IsWorking)
{
...
foreach(StepItem step in this.Job.Steps)
{
if (step.StepIsEnabled)
{
...
switch (step.StepType)
{
case "SQL" :
{
...
}
break;
case "PKG" :
{
try
{
// this should never happen, but we check nonetheless
if (string.IsNullOrEmpty(step.SsisFilePath))
{
...
}
else
{
string pkgDLLFileName = step.SsisFilePath;
string path = System.IO.Path.Combine(Globals.AppPath,
"SQLXPkgRunner.exe");
// we need to pass the package's (fully qlaified) filename,
// the step ID, and the step connection string to the
// SQLXPkgRunner app
string args = string.Format("-p\"{0}\" -s\"{1}\" -c\"{2}\"",
pkgDLLFileName,
step.ID,
step.ConnectionString);
// configure and start the app's process
Process app = new Process();
ProcessStartInfo info = new ProcessStartInfo()
{
Arguments = args,
CreateNoWindow = true,
FileName = path,
UseShellExecute = true,
};
app.StartInfo = info;
app.Start();
// we want to wait for it to exit
app.WaitForExit();
// and deal with the results.
int result = app.ExitCode;
if (result > 0)
{
status = "FAIL";
SQLXExceptionEnum exception = Globals.IntToEnum(result,
SQLXExceptionEnum.Unknown);
switch (exception)
{
case SQLXExceptionEnum.PkgFileNotFound :
reason = string.Concat(SQLXExceptionCodes.Codes[(int)exception],
" - ",
pkgDLLFileName);
break;
default : reason = SQLXExceptionCodes.
Codes[(int)exception] ; break;
}
}
else
{
status = "SUCCESS";
reason = string.Empty;
}
}
}
catch (Exception ex)
{
status = "FAIL";
reason = ex.Message;
}
// DebugMsgs...
}
break;
}
...
}
else
{
...
}
}
...
}
}
The SQLXPkgRunner Application
This application has no window or interface because it is essentially executed from within a Windows service. When the applications starts up, a few sanity checks are performed on the arguments.
static int Main(string[] args)
{
var options = new CommandlineOptions();
CommandLine.Parser.Default.ParseArguments(args, options);
Globals.SetExtensionFileSystemObjects(Assembly.GetExecutingAssembly());
// assume success
int result = 0;
// if we have arguments
if (args.Length > 0)
{
// if the package path is null/empty, error
if (string.IsNullOrEmpty(options.Package))
{
result = (int)SQLXExceptionEnum.CmdLineArgPkgFilename;
}
// if the specified dll does not exist, error
else if (!File.Exists(options.Package))
{
result = (int)SQLXExceptionEnum.PkgFileNotFound;
}
// if the step ID is null/empty, error
else if (string.IsNullOrEmpty(options.StepID))
{
result = (int)SQLXExceptionEnum.CmdLineArgStepID;
}
// if the step's connectionstring is null/empty, error
else if (string.IsNullOrEmpty(options.ConnectionString))
{
result = (int)SQLXExceptionEnum.CmdLineArgPkgConnString;
}
// make sure we can get the SQLXAgent connection string,
// but I don't remember why... :)
else
{
string connStr = "";
// result will be 0 (ok), 5 (config file not found),
// or 6 (conn string not found in config file)
result = GetSQLXConnString(ref connStr);
}
// if we get here and result is still 0, load the package and
// run it.
if (result == 0)
{
result = LoadDllAndRun(options.Package.Trim(),
options.StepID.Trim(),
options.ConnectionString.Trim());
}
}
else
{
result = (int)SQLXExceptionEnum.NoCmdLineArgs;
}
return result;
}
If everything checks out, we load and run the package assembly. It is assumed (and advisable) that a given package only contain ONE object derived from SQLXAgentPkgBase, because we only look for the first object we find, and that's the one we use. Most of the code in this method deals with possible exceptions.
private static int LoadDllAndRun(string path, string stepID, string connString)
{
int result = 0;
SQLXAgentPkgBase pkg = null;
// load the package DLL
var dll = Assembly.LoadFile(path);
// assume it's invalid
bool foundObject = false;
try
{
// see if an object derived from SQLXAgentPkgBase exists in the
// package DLL
Type type = dll.GetExportedTypes().FirstOrDefault(x=>x.BaseType.Name.IsLike("%SQLXAgentPkgBase"));
if (type != null)
{
// try to instantiate the object
pkg = (SQLXAgentPkgBase)(Activator.CreateInstance(type));
if (pkg != null)
{
// try to run the package
foundObject = true;
pkg.Run(stepID, connString);
string failReason = pkg.FailReason;
}
}
// can't find an appropriate class in the package asembly
if (!foundObject)
{
result = (int)SQLXExceptionEnum.SQLXPkgBaseClassNotFound;
}
}
// all of the exceptions that might be thrown here, and the return
// codes that are applicable
catch (BadImageFormatException)
{
result = (int)SQLXExceptionEnum.PkgRunnerBadImageFormat;
}
catch (System.IO.FileNotFoundException)
{
result = (int)SQLXExceptionEnum.PkgRunnerFileNotFound;
}
catch (System.IO.FileLoadException)
{
result = (int)SQLXExceptionEnum.PkgRunnerFileLoad;
}
catch (ArgumentException)
{
result = (int)SQLXExceptionEnum.PkgRunnerArgument;
}
catch (NotSupportedException)
{
result = (int)SQLXExceptionEnum.PkgRunnerNotSupported;
}
catch (System.Reflection.TargetInvocationException)
{
result = (int)SQLXExceptionEnum.PkgRunnerTargetInvocation;
}
catch (MethodAccessException)
{
result = (int)SQLXExceptionEnum.PkgRunnerMethodAccess;
}
catch (System.Runtime.InteropServices.InvalidComObjectException)
{
result = (int)SQLXExceptionEnum.PkgRunnerInvalidComObject;
}
catch (MissingMethodException)
{
result = (int)SQLXExceptionEnum.PkgRunnerMissingMethod;
}
catch (TypeLoadException)
{
result = (int)SQLXExceptionEnum.PkgRunnerTypeLoad;
}
catch (Exception)
{
result = (int)SQLXExceptionEnum.PkgRunnerUnexpected;
}
return result;
}
As you can see, the act of running a package is a fairly minor cog in the SQLXAgent machine. The most interesting part of it is loading the package DLL on demand, and making sure it has the expected derived class.
History
- 29 Sep 2017 - Initial publication.