Most of
the developers and DBAs know about two ways of deploying packages: You can deploy them to database server and run them using SQL Server Agent job or you can deploy
the packages to file system and run them using dtexec.exe utility. Both approaches have their pros and cons. However I would like to show you that there is a third way (sort of) that is often overlooked, and it can give you capabilities
the ‘traditional’ approaches can’t. I have been working for a few years with applications that run packages from host applications that are implemented in .NET. As you know, SSIS provides programming model that you can use to implement more flexible solutions. SSIS applications are usually thought to be batch oriented, with fairly rigid architecture and processing model, with fixed timeframes when
the packages are executed to process data. It doesn’t to be
the case, you don’t have to limit yourself to batch oriented architecture. I have very
good experiences with service oriented architectures processing large amounts of data. These applications are more complex than what I would like to show here, but
the principle stays
the same: you can execute packages as a service, on ad-hoc basis. You can also implement and schedule various signals, HTTP calls, file drops, time schedules, Tibco messages and other to run
the packages. You can implement event handler that will trigger execution of SSIS when a certain event occurs in StreamInsight stream. This post is just a small example of how you can use
the API and other features to create a service that can run SSIS packages on demand. I thought it might be a
good idea to implement a restful service that would listen to requests and execute appropriate actions. As it turns out, it is trivial in C#.
The application is implemented as console application for
the ease of debugging and running. In reality, you might want to implement
the application as Windows service. To begin, you have to reference namespace System.ServiceModel.Web and then add a few lines of code: Uri baseAddress = new Uri("http://localhost:8011/"); WebServiceHost svcHost = new WebServiceHost(typeof(PackRunner), baseAddress); try { svcHost.Open(); Console.WriteLine("Service is running"); Console.WriteLine("Press enter to stop
the service."); Console.ReadLine(); svcHost.Close(); } catch (CommunicationException cex) { Console.WriteLine("An exception occurred: {0}", cex.Message); svcHost.Abort(); }
The interesting lines are 3, 7 and 13. In line 3 you create a WebServiceHost object. In line 7 you start listening on
the defined URL and then in line 13 you shut down
the service. As you have noticed,
the WebServiceHost constructor is accepting type of an object (here: PackRunner) that will be instantiated as singleton and subsequently used to process
the requests. This is
the class where you put your logic, but to tell WebServiceHost how to use it,
the class must implement an interface which declares methods to be used by
the host.
The interface itself must be ornamented with attribute ServiceContract. [ServiceContract] public interface IPackRunner { [OperationContract] [WebGet(UriTemplate = "runpack?package={name}")] string RunPackage1(string name); [OperationContract] [WebGet(UriTemplate = "runpackwithparams?package={name}&rows={rows}")] string RunPackage2(string name, int rows); } Each method that is going to be used by WebServiceHost has to have attribute OperationContract, as well as WebGet or WebInvoke attribute.
The detailed discussion of
the available options is outside of scope of this post. I also recommend using more descriptive names to methods . Then, you have to provide
the implementation of
the interface: public class PackRunner : IPackRunner { ... There are two methods defined in this class. I think that since
the full code is attached to
the post, I will show only
the more interesting method,
the RunPackage2. /// <summary> /// Runs package and sets some of its variables. /// </summary> /// <param name="name">Name of
the package</param> /// <param name="rows">Number of rows to export</param> /// <returns></returns> public string RunPackage2(string name, int rows) { try { string pkgLocation = ConfigurationManager.AppSettings["PackagePath"]; pkgLocation = Path.Combine(pkgLocation, name.Replace("\"", "")); Console.WriteLine(); Console.WriteLine("Calling package {0} with parameter {1}.", name, rows); Application app = new Application(); Package pkg = app.LoadPackage(pkgLocation, null); pkg.Variables["User::ExportRows"].Value = rows; DTSExecResult pkgResults = pkg.Execute(); Console.WriteLine(); Console.WriteLine(pkgResults.ToString()); if (pkgResults == DTSExecResult.Failure) { Console.WriteLine(); Console.WriteLine("Errors occured during execution of
the package:"); foreach (DtsError er in pkg.Errors) Console.WriteLine("{0}: {1}", er.ErrorCode, er.Description); Console.WriteLine(); return "Errors occured during execution. Contact your support."; } Console.WriteLine(); Console.WriteLine(); return "OK"; } catch (Exception ex) { Console.WriteLine(ex); return ex.ToString(); } }
The method accepts package name and number of rows to export.
The packages are deployed to
the file system.
The path to
the packages is configured in
the application configuration file. This way, you can implement multiple services on
the same machine, provided you also configure
the URL for each instance appropriately. To run a package, you have to reference Microsoft.SqlServer.Dts.Runtime namespace. This namespace is implemented in Microsoft.SQLServer.ManagedDTS.dll which in my case was installed in
the folder “C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies”. Once you have done it, you can create an instance of Microsoft.SqlServer.Dts.Runtime.Application as in line 18 in
the above snippet. It may be a
good idea to create
the Application object in
the constructor of
the PackRunner class, to avoid necessity of recreating it each time
the service is invoked. Then, in line 19 you see that an instance of Microsoft.SqlServer.Dts.Runtime.Package is created.
The method LoadPackage in its simplest form just takes package file name as
the first parameter. Before you run
the package, you can set its variables to certain values. This is a great way of configuring your packages without all
the hassle with dtsConfig files. In
the above code sample, variable “User:ExportRows” is set to value of
the parameter “rows” of
the method. Eventually, you execute
the package.
The method doesn’t throw exceptions, you have to test
the result of execution yourself. If
the execution wasn’t successful, you can examine collection of errors exposed by
the package. These are
the familiar errors you often see during development and debugging of
the package. I you run
the package from
the code, you have opportunity to persist them or log them using your favourite logging framework.
The package itself is very simple; it connects to my AdventureWorks database and saves number of rows specified in variable “User::ExportRows” to a file. You should know that before you run
the package, you can change its connection strings, logging, events and many more. I attach solution with
the test service, as well as a project with two test packages. To test
the service, you have to run it and wait for
the message saying that
the host is started. Then, just type (or copy and paste)
the below command to your browser. http://localhost:8011/runpackwithparams?package=%22ExportEmployees.dtsx%22&rows=12 When everything works fine, and you modified
the package to point to your AdventureWorks database, you should see "OK” wrapped in xml: I stopped
the database service to simulate invalid connection string situation.
The output of
the request is different now: And
the service console window shows more information: As you see, implementing service oriented ETL framework is not a very difficult task. You have ability to configure
the packages before you run them, you can implement logging that is consistent with
the rest of your system. In application I have worked with we also have resource monitoring and execution control. We don’t allow to run more than certain number of packages to run simultaneously. This ensures we don’t strain
the server and we use memory and CPUs efficiently.
The attached zip file contains two projects. One is
the package runner. It has to be executed with administrative privileges as it registers HTTP namespace.
The other project contains two simple packages. This is really a cool thing, you should check it out!
