Wednesday, January 04, 2006

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.

5 Comments:

Blogger scroop said...

OMGWTFBBQ I would LOVE for you to write a tutorial on multi-threading. I could darn so many more socks in a single evening if I didn't have to do them on eat a time.

2:56 AM  
Blogger Leedrick said...

This link here should help you scroop.

Good luck with the socks!

5:43 PM  
Anonymous Anonymous said...

Hi Leedrick,
I've just tested your coude but I get a conversion error.
I try to call a DTSX package from the file system store (SSIS).
I can resolve the ID of the package but I can't run it from my asp.net application. I'm working on this for a couple of days now but I just don't get there.
any idea?
thanks in advance,
Kristof

6:37 AM  
Blogger Leedrick said...

Hi Kristof,

I don't have any ideas off the top of my head, but if you post a snippet of your code, the actual exception details and the line on which it is being thrown I will look at it further.

Lee.

5:48 PM  
Blogger SMS API PHP said...

Thanks. In order to use SMS API C# format, you can go with Wegus msgclub SMS gateway API and integrate into your system or application. It is the fastest way to communicate with your SMS and website.

4:26 AM  

Post a Comment

<< Home