Using DTS from C#

During last few months I was busy doing different (mostly boring) projects and in one of them some DTS stuff was involved. After searching internet for examples I found few on Microsoft's How To and two examples on the rest of the web (one was in Italian). That is main motivation to write this article. To immediately make it clear, I didn't find solution for CustomTask problem and I don't have desire to do it, in this article I will concentrate on enumerating, executing and changing properties or global variables of DTS package. Since I'm using desktop engine I will also skip repository packages. For the start we will enumerate packages which are stored on (local) or (local)\NetSDK server as local packages. Key for handling DTS in .NET is adding reference to "Microsoft DTSPackage Object Library". If you don't know how to do it, it's nicely described in DotNETCookBook.pdf which is available for download on http://SQLDev.Net/DTS/ DotNETCookBook.htm. It also explains how to sign assembly and register it with GAC which is a question for new MCSD exams. For enumerating local packages on SQL Server we will use the following code:

static void Main(string[] args)
{
DTS.Application App=new DTS.Application();
DTS.PackageSQLServer package= App.GetPackageSQLServer("(local)\\NetSDK", "sa", "", DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default);
DTS.PackageInfos Infos;
Infos = package.EnumPackageInfos("", false, "");
foreach(DTS.PackageInfo Info in Infos)
{
Console.WriteLine("Name : {0}\tVersionID : {1}",Info.Name,Info.VersionID);
}
Console.Read();
}

First interesting thing here is DTS.Application which is actually "public interface Application" according to Object Browser. So we are creating our instance of that interface and using it to create our package object (PackageSQLServer) which is again interface. Further we are getting our infos and retrieving two out of 11 possible properties. Certainly I was quite surprised that we can create instance of interface simply by calling constructor of that interface. If we open EXE with ILDASM we will find:

newobj instance void [Interop.DTS]DTS.ApplicationClass::.ctor()

So it is really a constructor. Of course next in queue to be examined is Interop.DTS.dll (RCW created by VS.NET)

.class interface public abstract auto ansi import Application implements DTS._Application
{
.custom instance void [mscorlib]System.Runtime.InteropServices.GuidAttribute::.ctor(string) = ( 01 00 24 31 30 30 33 30 30 30 31 2D 45 42 31 43 2D 31 31 43 46 2D 41 45 36 45 2D 30 30 41 41 30 30 34 41 33 34 44 35 00 00 ) // 04A34D5..
.custom instance void [mscorlib]System.Runtime.InteropServices.CoClassAttribute::.ctor(class [mscorlib]System.Type) = ( 01 00 14 44 54 53 2E 41 70 70 6C 69 63 61 74 69 6F 6E 43 6C 61 73 73 00 00 ) // onClass..
} // end of class Application

So we must be careful with Object Browser or IntelliSense when we are using unmanaged libraries in future. Procedure to enumerate what is in structured storage is almost identical :

static void Main(string[] args)
{
DTS.Package2Class package=new DTS.Package2Class();
DTS.SavedPackageInfos Infos = package.GetSavedPackageInfos("C:\\Pubs2Pubs2Package.dts");
foreach(DTS.SavedPackageInfo Info in Infos)
{
Console.WriteLine("Name : {0}\tVersionID : {1}",Info.PackageName,Info.VersionID);
}
package.UnInitialize();
Console.Read();
}

Number of properties is somewhat smaller for info but treatment is about the same. Package2Class is object which we are going to use for execution or manipulation of properties.

static void Main(string[] args)
{
DTS.Package2Class package=new DTS.Package2Class();
try
{
Object MIA=System.Reflection.Missing.Value;
package.LoadFromSQLServer("(local)\\NetSDK", "sa", "", DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "", "{F580DFC4-1F6A-4D70-B6A1-1C200B0D7890}", "CustomTaskPKG",ref MIA);
Console.WriteLine(package.Description);
package.Description="My description";
Console.WriteLine(package.Description);
}
catch(Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
package.UnInitialize();
package=null;
}
Console.Read();
}

If you want the freshest version of the package than just omit VersionID. To save new description use :

package.SaveToSQLServer("(local)\\NetSDK", "sa", "", DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "","",ref MIA,false);

For global variables procedure is again very similar.

static void Main(string[] args)
{
DTS.Package2Class package = new DTS.Package2Class();
try
{
Object MIA=System.Reflection.Missing.Value;
package.LoadFromStorageFile("C:\\Pubs2Pubs2Package.dts","","","","Pubs2Pubs2Package",ref MIA);
package.GlobalVariables.AddGlobalVariable("MyVariable","MyValue");
foreach(DTS.GlobalVariable GVar in package.GlobalVariables)
Console.WriteLine("Name : {0}\tValue : {1}",GVar.Name,GVar.Value);
}
catch(Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
package.UnInitialize();
}
Console.Read();
}

To save changes to file use :

package.SaveToStorageFile("C:\\Pubs2Pubs2Package.dts","","",ref MIA,false);

Finally if you want to change value of some variable use :

package.GlobalVariables.Item("MyVariable").let_Value("Hello from C#");

To execute package use :

package.Execute();

As you noticed so far the number of lines of code to achieve any of these operations is extremely small. This covers some simple manipulations of existing DTS package(s). Thanks to reach System.Collections namespace it's easy to manipulate DTS packages and their belonging properties and variables and create associations between packages, servers, CSV files and so on. As a matter of fact I have an application written in VB.NET ( as well as one in C#) which does that, so if anybody is interested in buying it my e-mail is at the bottom of the page.