• Download SQLXAgent_Final.zip - 1.1 MB

Part 1 - Using SQLXAgent (this article)
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
Part 6 - Interesting Coding

Introduction

I suspect many of us use SQL Server Express. I also suspect that like me, many of you wished that SQL Server Express implemented Agents. This article describes a utility I've written to address that shortcoming, and I call it SQLXAgent. This code has been under on-and-off development for over a year, and has been completely re-designed at least twice that I can remember. I do some of my best design and coding during my morning shower, and my wife has endured several (dozen) of my expletive-filled rinse cycles as a result of the ideas and realizations I come up with regarding SQLXAgent (which caused the afore-mentioned redesigns).

All of the code developed for this utility was done with Windows 7, VS2013 utilizing .Net 4.5, WPF (for the UI stuff) and the star of our show, SQL Server Express 2012. If you're using anything else (older or newer), and run into problems, you know the drill - be a programmer and resolve your own issues, because unless I reconfigure my entire dev box to match yours (which I'm not going to even *briefly* consider), I cannot possibly help you. I utilized some .Net features that are only available beginning with .Net 4.5, so you're likely to have problems if you're not using tools at least as modern as mine.

Finally, this is a long article (mostly) because of all the screen shots, and because I speak fairly proper English, so expect complete sentences without regard for brevity. This article should be considered a basic user's guide. Before you do anything else, at least read this section to get a gist of what to expect. If you want a more behind-the-scenes look at the application, please review the subsequent parts of this article series. I've broken it up into sections that I think present a logical separation of interests. Links are at the top of this article (and at the top of each article in this series).

Assumptions

I assume that everybody here is fairly intelligent, and somewhat perceptive with regards to the use of most application software. I don't believe in straying too god-awful far from the old Windows application design guide where the user interface is concerned, so even the least astute of you should be able to grasp the meaning of the controls I use, as well as their purpose for being. With that said, I don't like to overstate the obvious, and in a lot of cases, I mention a given control's functionality/purpose in passing, and I may not go into an in-depth discussion into these topics. So, if you need coddling beyond the narrative presented in this article, or a gentle guiding hand to help you over the rough spots, you should probably look for someone more skilled with the English language and/or more experienced using Windows applications to help you over the rough spots. Besides all that, I lost my coddling hand during a past armed conflict with rogue factions of typeless langauge advocates, and replaced it with a purely mechanical hand that feels nothing, and is most likely to crush the dreams of anyone whom it thinks is standing in the way of my eventual domination of the world. (insert maniacal laughter here)

This utility is comprised of several assemblies (executables and DLLs), all of which must exist in the same folder. For my purposes, I found it easiest to simply run everything from the bin\debug or bin\release folder in the solution's hierarchy, but that's because my SQL Express instance is on my dev box. If you want to run the SQLXAgent.exe application remotely, there are many available methods for doing so. Keep in mind that it's a WPF application, so graphic performance in your chosen remote control software is fairly important. There will also be security concerns that you may have to deal with. The remoting software and security concerns (if any) are firmly outside the scope of this article series. It may also require that you make changes to the code in this regard (this is one of the places where my "be a programmer" comes into play).

Since we're talking about SQL Server Express, I've made some assumptions regarding its use. Namely you will be placing this utility on the box that's actually running SQL Server Express. Not doing so introduces a number of security issues with regards to managing the SQLXAgent database and the SQLXAgentSvc windows service. I also assume that you're in the admin group on said machine. Again, we're talking SQL Server Express, which is NOT indtended to be used in an enterprise environment.

Notice: To accommodate CodeProject's image size "best practice", I resized all of the screen shot images to 640 pixels wide. I also edited as much white space out of the images as possible. This means that when you actually run the app, the forms will generally be much larger and easier to read than the screenshots.

What SQLXAgent DOESN'T Do

Because I (rightfully) don't expect people to be using SSQL Express in an enterprise environment, I've taken the liberty of NOT supporting some of the stuff you get with SQL Server jobs.

• Targets and Alerts - These seemed fairly pointless given the limited scope defined by your choice of SQL Express. Targets allow you to target mutiple database servers or just the one in which the job is defined, and Alerts allow you to perform certain actions (including execution of jobs) based on events generated by SQL Server.
   
• Starting at a step - There is no support for specifying that a multi-step job should start at a specific step. Instead, all jobs start at step 1, and steps that are disabled are not executed.
   
• Progression from one step to the next in a multi-step job is controlled by the step's Enabled flag. All steps that are enabled will be executed in the sequence specified until the job fails, or all of the steps have been processed. This means you can't tell a step how to proceed like you can in SQL Server's jobs.
   
• Multiple schedules - each job has ONE job schedule instead of one or more.
   
• Scheduling type - SQL server allows both one-time and recurring scheduling types, while SQLXAgent only supports "Recurring" schedules. If you want to run a job once, you can manually run the job from the main SQLXAgent window.
   
• Step types - only two step types are available in SQLXAgent - SQL and "Package". SQL steps execute the SQL query you specify, while packages run the custom package assemblies that you create. Packages can be written in your favorite .Net language, which should be C# if you're normal like me.
   
• Step Database combo box - instead of specifying just the database, you MUST specify the entire connection string.
   
• Steps Advanced page is not implemented.
   

There may be other things the app doesn't support, but they've slipped my mind, so consider this to be an opportunity to explore.

Moving Parts

At the very minimum you have to install the service, and configure at least one job. Fortunately, both of those functions can be handled by the main SQLXAgent.exe application. If your agent jobs require something more complex than SQL queries, you must develop a package with your favorite (and most appropriate for you) .Net language.

I've found that most of my SSIS package development at work is importing Excel or CSV files, so I've included reasonably robust code for doing exactly that within your custom packages. There's no fancy UI like the BIDS stuff in Visual Studio, but any programmer that is even slightly talented should be able to wrangle such a task with the code I've provided.

Before You Start Using SQLXAgent

To keep the download as small as possible, as well as enforce a couple of points later in the article, I did not include binaries in the download. This means you need to download and the SQLXAgent solution, and build it at least once.

Once again, I'm using Visual Studio 2013 on Windows 7, and the database components were implemented on SQL Express 2012. If you're running newer tools, you might encounter issues with regards to your tool set. I'm not saying this will definitely occur, but given our litigious snowflake society, I figured I should cover all my bases. So, gird your loins, and download/build the code.

Finally, this is a rather complex application with a lot of moving parts, and I'm only one (admittedly lazy) person with a worse-than-swiss-cheese memory. There may be some mispellings in the article or in the forms that I missed, and possibly even some stuff that doesn't work as planned/described, or even at all (GAK!). Simply let me know AFTER you've reviewed the article history at the bottom of this page, because I may have already found and fixed what you found, and updated the article as a result.

Configuration - SQLXAgent.exe

Configuration is handled within a single application - SQLXAgent.exe

All of the configuration tasks (including managing the service) are handled by this WPF application, and this is also the first app you will run. The first time you run it, you will be presented with the following form:

All this form is telling you is to not expect too much because it's not intended for use within an enterprise environment (we are talking about SQL Express here, so whining about this will not be tolerated). If you don't want to see this form again, check the Do not show again checkbox before clicking the Okay button.

NOTE: Every time you do a Clean/Rebuild on the solution, it resets the app.config file, so you'll see the notice again the first time you run the application after doing so.

The first time you've run the app, it will present you with the Settings form:

This form requires that you provide the server name, and the SQL server instance name, so that a proper connection string can be created. The default server name is the name of the machine on which you're running the application. The SQL server instance name is also assumed to be the default used by the SQL Server Express installer - "SQLEXPRESS".

Notice as you enter that information, the connection string in the bottom half of the form is updated. Once you've provided those pieces of information, you can click the associated Test button. Assuming you specified a valid server name and instance, the form will display something like the following (the exception displayed should be something about the expected server login - "SQLXAgentUser" - not being found):

The bottom section of the form is still disabled. This is because the server must test as valid, (and you need to create the database) before you can test the resulting connection string. Once your server test succeeds, and if you haven't yet created the database, click the Create SQLXAgent Database button. the following form will be presented:

you must click the Test button for the connection string. If it fails (and it should the first time you do this), you are presented with the INVALID SERVER!, as well as the exception that was raised. In the case of our first run through this process, it will complain about the database user not being implemented.

Along with the exception, a button is displayed that allows you to create the database. Clicking that button will display the Create SQLXAgent Database form:

Note: I fixed the spelling of "components" after this screen shot was taken.

The process of creating the database will perform the following steps in the order listed. As each step is performed, the list view in the form will be updated. If a step fails, the entire process stops, and you have to figure out what went wrong.

To make it easier to determine exactly where a problem might occur, all of the steps below that require a SQL query to be performed, retrieve that query from an embedded resource file. If you want to review these files, load the source code into Visual Studio, and they can be found in the SQLXAgent\SQLXAgentQueries folder. To enhance problem resolution, each table and stored procedure is created from its own embedded resource query file. The steps below will indicate which query file is used to perform the action.

To give the creation of the database the best possible chance of succeeding, we have to take the precautionary steps of stopping services, closing apps, and then dropping the database and associated database objects. I refer to this as the "Stop-n-Drop" phase. This phase is comprised of the following steps (listed in the sequence in which they occur.)

• Stop the service if it's running. If this is the first time you've run SQLXAgent.exe, it shouldn't be running (in fact, it shouldn't even be installed yet).
   
• Shut down SQL Management Studio if it's running. You may be prompted by that application to save any queries you may have open and modified. My advice is to shut that app down manually before you create the SQLXAgent database, but that's certainly not a requirement.
   
• Drop the database if it exists. If this is the first time you've run SQLXAgent.exe, it shouldn't already exist. (Uses the SQLXAgent_DropDatabase.sql query file.)
   
• Delete the existing SQLXAgent .MDF and .LDF files if they weren't deleted when the database was dropped (I found that sometimes this might happen).
   
• Drop the SQLXAgentUser server login if it exists. If this is the first time you've run SQLXAgent.exe, it shouldn't already exist. (Uses the SQLXAgent_DropServerLogin.sql query file.)

After the database has been dropped, we can then create all of the required components.

• Create the SQLXAgent database. The query for this step is stored in the file SQLXAgentQueries\SQLXAgent_CreateDatabase.sql. (Uses the SQLXAgent_CreateDatabase.sql query file.)
   
• Add the SQLXAgentUser server login. This is required so that the SQLXAgentSvc windows service can access the database. We're not hyper-concerned with security because *this is SQL Express and we're not using it in an enterprise environment*. The password for this account is "SQLXAgent". (Uses the SQLXAgentQueries\SQLXAgent_CreateServerLogin.sql file.)
   
• Add the SQLXAgentUser server login user as a database user. Again, this allows the SQLXAgentSvc windows service to access the database. (Uses the SQLXAgentQueries\SQLXAgent_CreateDbUser.sql file.)
   
• Add the tables for the database. (Uses the SQLXAgentQueries\SQLXAgent_Table_*.sql files. There were five at the time of this writing.)
   
• Add the stored procedures for the database. (Uses the SQLXAgentQueries\SQLXAgent_SP_*.sql files. There were 24 at the time of this writing.)
   

As the process runs, the list box on the form will reflect the result of each step. Individual tables and stored procedures will report their own result. If a step fails, the process stops and will not proceed to the end. If this happens, you have to figure out what went wrong, address the problem, and re-run the creation process. There shouldn't be any issues with the SQL files themselves (famous last words, right?). The last entry in the list view should be something like "Process Complete". Here's a look at the form after the Create Database button is clicked, and the process is complete.

After the create process is performed, click the Okay button, and then click the connection string Test button in the Settings form. Assuming the creation process was completed successfully, the test should also be successful, and you can click the Okay button. In a perfect world, this *should* be the last time you have to even see the Settings form for a given instance of SQL Server Express. When your Settings form looks like this, you can click the Okay button, or move on to the email settings page.

If you intend to send email notifications for certain job status possibilities, you will need to specify some properties necessary to send an email.

The password is stored in encrypted form in the app.config file.

There are some miscellaneous settings you can change as well. I think the screen shot below is pretty much self-explanatory.

After you click Okay in the Settings form, the main window should look like this.

Form topology can be broken down into three obvious sections:

• The menu - this is your standard Windows-style menu. Exit should be self-explanatory (I hope), and we've already been in the Settings form, so Settings should also be self-explanatory. The other items will be discussed later in this article.
   
• The list view - This shows the jobs that you've specified.
   
• The buttons - These buttons allow you to add new jobs, or edit, delete, and run the currently selected job. If a job isn't selected only the Add button is enabled.

When you click this Add button, the Add/Edit Job form is presented.

Adding Jobs

A job is essentially a database-related task, ostensibly scheduled to occur on a repeated basis at the specified interval. In order to make this utility useful, you are expected to create one or more jobs. A given job can have one or more steps, and these steps (when more than one are specified), are executed in the order in which they positioned in the list of steps). When a step fails, processing for the job is halted and no subsequent steps are processed for the current job execution cycle. A step must also be enabled in order to run.

To the extent possible, the job configuration pages were made to resemble the same pages that exist in the Agent configuration component in the enterprise version of SQL Server.

When a new job is added, it is initially disabled, has a name of "New Job", is scheduled to occur weekly, and has one step already added (that you will have to edit in order to make it useful).

The General Page

This page simply allows the user to specify a job name, and either enable or disable the job. If the job isn't enabled (it's disabled by default), it will not be loaded or run by the service, so don't forget to enable it.

Those of you familiar with SQL Server's version of this page probably noticed

The Steps Page

This page displays the steps specified for the job, and allows you to change their sequential order, and add, edit, or delete them. The Add and Edit buttons both present the same form.

Here, you specify a name for the step, enable it, and pick an appropriate connection string for it. The program will retain the last ten connection strings specified and present them in a combo box so you don't have to re-type the same one over and over. If the one you need isn't in the list, click the Add button, and this form will be presented.

Like the Settings form, you must use the Test button to validate the specified connection string, and if it validates, you can click the Okay button. When you click Okay, you will be returned to the previous form and the newly added connection string will be available for selection for that step.

The following image illustrates that a SQL step has been configured.

While writing this application, I learned that the SQL GO command is not supported in a batch SQL query (which is what this is to SQL Server). If you need/want to use GO, I recommend that you write a stored procedure in your database, and EXEC that stored procedure in a SQL step.

This form allows you to name the step (the default name is "New Step"), enable the step (it is not enabled by default), and specify what type of step it is. Job steps can be one of two available types:

• SQL - represents a step that runs a SQL query. Any valid SQL is acceptable. It is strongly recommend that you test your SQL query in SSMS before specifying it here.
   
• PKG - represents a step that runs a developer-provided package. The desired package assembly MUST exist before you can specify its use here. Please refer to the section below titled Creating a Package.
   

If you hover over the Step Content column, a tooltip will be displayed showing all the content. In the case of a SQL step, formatting will be retained for the tooltip. Also, if you have more than one step specified, you can move them up/down in the sequence. Because we're using SQL Express, all jobs start at step 1, and if a step fails, subsequent steps for that job are not executed, and the job is marked as failed in the audit log.

The Schedule Page

The schedule page looks almost exactly like one you see in the enterprise-level SQL Server. There are a few minor differences, and they are indicated below.

The Notifications Page

The Notifications page allows you to configure how error notifications are to be communicated.

Manually Running a Job

To manually run a job, select the desired job, and click the Run button at the bottom of the main window. The Manual Job Progress form will be presented.

Simply click the Start button to run the job. You won't be able to click the Okay button while the job is running. Execution status will be displayed in the form's list view as the job's steps are executed. After running the job, it will show up in the job history as being run by the USER. (Allowing the service or the TPLTesterUI app to run the job via its schedule will cause the Run By column to display SERVICE.)

Managing the Service

Management of the service is performed via the Service menu item at the right side of the form menu. Just to the left of this menu item is an ellipse, whose color indicates the current state of the service, as indicated below.

• the service is not installed
   
• the service is installed, but is not running
   
• the service is installed, and is running

The sub-menu items in this menu are enabled based on the current state of the service. For instance, you can't start or uninstall the service if it's not installed, and you can't stop the service if it's not running.

You must install and start the service in order to start your job schedule(s).

Job History

Like in SQL Server, SQLXAgent tracks job history, and you can view it by clicking Job History in the menu. When you do, the following form is presented.

To view the steps associated with a specific job, simply click the job in the history List view. The item will be expanded and you can inspect the steps. When you select a job in the list box, or refresh the list, all previously expanded items in the list view will be collapsed. If a job fails, the fail reason will not be displayed for the job. You have to expand the job to show the steps, and inspect the failed step.

History items are retained for 30 days. The JobManager object calls a stored procedure once every day at 00:00:00 to delete expired history items. By default, SQLXAgent retains history items for up to 30 days. If you want to reduce that value, use the Settings form (described above). If you change the value while the JobThreadManager object is running (either from within the service or within the TPLTesterUI application), the new value will be loaded and used at the next deletion cycle.

DB Maintenance

The DB Maintenance menu item simply provides convenient ways to start over with the data. The buttons describe what they do.

Creating a SQLXAgent Package

Creating a package requires that you draw on your mad coding skills. Simply put, the reason is that I didn't want to invest the time/effort to create a whiz-bang UI like SQL Server has, so ALL SQLXAgent packages are essentially the equivalent of a script task. The benefit of doing this is that your SQLXAgent package can be written with any .Net language you might prefer, and can do anything a regular .Net DLL can do. There are a few sample packages provided in the solution.

Before you suggest it, yes, it's possible to create a DTSX package with the BIDS stuff and run it from a .Net app, but no, running it on a SQL Express instance is not possible, because SQL Express does not have the assemblies required to actually run a DTSX package (remember - that's why we're all here).

Getting Started

Creating a "package" for SQLXAgent's use is actually quite simple. Follow these steps to write your own packages:

• Open the SQLXPackages solution - start Visual Studio, and open the SQLXPackages solution.
• Create a new project - Create a new class library project, and give it an appropriate name.
   
• Edit the project properties - The following changes need to be made to the project properties.
   
  • On the Build tab - For both the Debug and Release configurations, add "..\" to the Output path field
     
  • On the Build Events tab - Add the following lines to the Post build event command line field:
    CD $(TargetDir)
    DEL $(TargetName).PKG
    REN $(TargetFileName) $(TargetName).PKG

• Add References - Add the following references to the project. Both of these assemblies exist in the \SQLXAgent\DLLBin folder, so you'll have to click the Browse... button to add them.
   
  • SQLXCommon - contains handy object extensions and most importantly, the DBOject2 class that allows handy SQL methods. Including this reference is optional, but you'll be glad you did.
     
  • SQLXPkgBase - REQUIRED to make a SQLXAgent-compatible package assembly.
     
• Inherit from the required base class - Inherit your package class from the abstract class SQLXPkgBase.SQLXAgentPkgBase.
   
• Implement the Run method - This is the only abstract method in the class. Copy the boilerplate code shown below into your class to make it easy on yourself.
  C#

  public override void Run(string stepID, string connString)
  {
      // The id of the step being executed - you can use this to retrieve any info 
      // about the job/step
      this.StepID               = stepID;
  
      // This is the connecion string to the SQLXAgent database. It is passed to 
      // this package by the SQLXPkgRunner application
      this.SQLXConnectionString = connString;
  
      // These properties are found in the base SQLXAgentPkgBase class, and can be
      // accessed from the calling application. You should set these properties as 
      // appropriate. The default settings used below indicate a successful outcome.
      this.DLLResult            = 0;
      this.FailReason           = string.Empty;
  
      // TO-DO: add your stuff after this line - set the DLL result to either 0
      //        (success) or 1 (fail) before you exit this method. There are a
      //        couple of example methods for importing from EXCEL and CSV files.
  }

• Add your code - adding your code is required, but what you implement, and how you do it is completely up to you. Typically, a package will import data, move data, and download data and then import it, watch a file system folder for changes, or any number of other ways to trigger some sort of database operation.

Testing Your Packages

I included a command line application in the SQLXPackages solution called PackageTester with which you can test your packages before using them in a job. To test a package, you simply have to add the assembly reference for the package, and add a couple of lines to the Main method to run it. It's really just that simple. Below, you'll see an example of the contents of the Main method I used to test the CSV importer package.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PacakgeTester
{
    class Program
    {
        static void Main(string[] args)
        {
            PkgImportCSV importer = new PkgImportCSV();
            importer.Run("step ID goes here", 
                         @"Server=mars\SQLEXPRESS;Database=SQLXAgent;Integrated Security=SSPI;");
        }
    }
}

I discovered that the original post-build step I was using to create "PKG"" assemblies (renaming the compiled .DLL to .PKG) was preventing the PackageTester application from loading the package assemblies (it insisted on loading a DLL), so I had to change the "REN" in the post-build step to "COPY /Y".

Importing Excel Spreadsheets and CSV files

One of the most handy aspects of SQL Server's SSIS package builder is that you can rather easily import Excel worksheets and CSV files. SQLXAgent makes this, I think, just as easy to implement this kind of functionality, especially for people that are developers (at whom this utility is actually targeted). Details regarding the development of importers are described in Part 3 of this article series.

History

• 27 Feb 2018 - Fixed some spelling errors and added some sarcasm. I also noticed that a screen shot is missing. I'll endeavor to find out where the missing image is.
   
• 05 Oct 2017 - Style changes
   
• 29 Sep 2017 - Initial publication.