• Download SQLXAgent_Final.zip - 1.1 MB

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.