One of our clients has hired us to pick up and run with an existing web application that their former development house had to walk away from. It's a hodge podge of programming best practices and some questionable architecture and design decisions, but that's a topic for another entry.

One of our tasks was to research a documented bug concerning a spreadsheet upload and data not being updated on an ad hoc basis (basically when the spreadsheet uploaded, the table that the spreadsheet imported into was not updating). On further inspection I noticed that there was no listener on the directory that the spreadsheet was being uploaded to, and there were some significant problems with the DTS job that was doing the heavy lifting for the import.

My solution was to implement a simple ASP.NET form with a file upload control and tap into the SQL Server 2005 DTS libraries. In just a few lines of code, the spreadsheet is uploaded, and the DTS job is fired.

The first thing you'll want to do (after creating a project) is set a reference to the DTS Object Library. Its an entry under the COM tab. You want the DTS Runtime 1.0 library. Adding this through Visual Studio will take care of all the work required to make the Interop wrapper; if you're using a different IDE run the appropriate commands.

After its added, your Bin folder should contain the Interop file (if you can't see your Bin folder, turn it on by right clicking the project and selecting "Add ASP.NET Folder > Bin").

(NOTE - My formatting tools simply will not format code properly. Please reference the link at the end of this post, which contains the source, and follow along...)

Now that you have the right type library added, the rest is a simple matter of creating a wrapper class that holds your code. I created a small class called DTS Wrapper with an overloaded constructor that accepts the target SQL Server server name and login credentials. You can extend this to include any specific DTS package credentials also.

Once that's done, it as easy as calling two methods to fire your DTS job. The first, LoadFromSQLServer, accepts 9 parameters that are mostly unremarkable except for the last one,  pVarPersistStgOfHost, which will not be used in this exercise so you can just create a dummy null object for it. 

The second function is the Execute call, which tells the job to fire. In my function below, you'll notice an argument being passed, PackageName, which is a String - this is the name of your DTS package exactly as it appears in SQL Server.  

The guts are pretty self-explanatory, except for the loop in the Try block. If you've ever executed a DTS job manually, you know that your job may have multiple steps. Each step returns a status that is checked before the next stage to determine whether or not to perform a particular workflow step. The loop goes through the Steps collection, held by the DTS class, and checks the ExecutionResult (an Enum) and ensures that the result is NOT failure. If so, we log the error and throw it for trapping in our Finally block. 

This loop is key, since workflow steps that will fire on Completion or on Failure will also be checked here. If you've put your DTS package together well, you'll know at any time what the status is and where it failed. This is a good supplement to your DTS log files as well.

The rest is just tidying up our objects and calling it a day. You can also see that I make use of the ExceptionManagement classes, which are a must have in my opinion.

Your comments and improvements are strongly encouraged! If you find this useful, I'd love to know. The source includes the Interop wrapper, a sample form, and the DTSWrapper, all in C# 2.0. The web form checks and does all the work with an Excel spreadsheet, but you can change this to work with any valid DTS data import source (Access, CSV, etc.)

Download the source files (63 KB ZIP file)