Executing an Integration Service Using c#

Today I decided to create a program that would execute some SQL Server 2005 Integration Services (formerly DTS) in preparation for an upcoming project.

Unfortunately due to the product being so new, there was limited information on the web and it took me a little while to figure it out.

So that other developers don't have to hunt around like I did, I thought I would create a blog and post an example. Hopefully I will find other useful things to post here in the future.

I found this link which was written for Sql Server 2000. Unfortunately the class library has changed for 2005, but I still found it helpful as a starting point.

You can see the whole Integration Service class library defined here. Even though DTS has changed its name in Sql Server 2005, you will still find it mentioned in the class library methods.

Before starting, you will need to provide a reference to Microsoft.SQLServer.DTSRuntime.Wrapper in your project. Also add the following using declaration:

using Microsoft.SqlServer.Dts.Runtime.Wrapper;

Here is the code I used to get a list of the packages on the server:

private void GetPackageList()
{
  Microsoft.SqlServer.Dts.Runtime.Wrapper.Application app;
  app = new Microsoft.SqlServer.Dts.Runtime.Wrapper.Application();
  //get package info object collection from the Integration Server
  IDTSPackageInfos90 infos = app.GetDtsServerPackageInfos("[directory ie. 'file system']", "[your server name]");
  foreach (IDTSPackageInfo90 info in infos)
  {
    MessageBox.Show(info.Name);
  }
}

Executing a package can be done like so:

private void ExecuteCustomerImport()
{
  Microsoft.SqlServer.Dts.Runtime.Wrapper.Application app;
  app = new Microsoft.SqlServer.Dts.Runtime.Wrapper.Application();
  IDTSPackage90 package = app.LoadFromDtsServer(@"[directory]\[package name]", "[your server name]", true, null);

  package.Execute();
}

I have shown how to access a service stored in the file system here, but if you want to access Sql Server stored packages, just use the LoadFromSQLServer method instead of LoadFromDtsServer.

Obviously due to the time it can potentially take to run an Integration Service, you will need to use multi-threading to allow your program to run efficiently. There are many resources on the web showing how to do this in c#, but if you would like me to write a post on it, let me know.

I hope you found this useful. Please feel free to post any comments.