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.
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
{
if (string.IsNullOrEmpty(step.SsisFilePath))
{
...
}
else
{
string pkgDLLFileName = step.SsisFilePath;
string path = System.IO.Path.Combine(Globals.AppPath,
"SQLXPkgRunner.exe");
string args = string.Format("-p\"{0}\" -s\"{1}\" -c\"{2}\"",
pkgDLLFileName,
step.ID,
step.ConnectionString);
Process app = new Process();
ProcessStartInfo info = new ProcessStartInfo()
{
Arguments = args,
CreateNoWindow = true,
FileName = path,
UseShellExecute = true,
};
app.StartInfo = info;
app.Start();
app.WaitForExit();
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;
}
}
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());
int result = 0;
if (args.Length > 0)
{
if (string.IsNullOrEmpty(options.Package))
{
result = (int)SQLXExceptionEnum.CmdLineArgPkgFilename;
}
else if (!File.Exists(options.Package))
{
result = (int)SQLXExceptionEnum.PkgFileNotFound;
}
else if (string.IsNullOrEmpty(options.StepID))
{
result = (int)SQLXExceptionEnum.CmdLineArgStepID;
}
else if (string.IsNullOrEmpty(options.ConnectionString))
{
result = (int)SQLXExceptionEnum.CmdLineArgPkgConnString;
}
else
{
string connStr = "";
result = GetSQLXConnString(ref connStr);
}
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;
var dll = Assembly.LoadFile(path);
bool foundObject = false;
try
{
Type type = dll.GetExportedTypes().FirstOrDefault(x=>x.BaseType.Name.IsLike("%SQLXAgentPkgBase"));
if (type != null)
{
pkg = (SQLXAgentPkgBase)(Activator.CreateInstance(type));
if (pkg != null)
{
foundObject = true;
pkg.Run(stepID, connString);
string failReason = pkg.FailReason;
}
}
if (!foundObject)
{
result = (int)SQLXExceptionEnum.SQLXPkgBaseClassNotFound;
}
}
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.