Executing SQL Server Data Transformation Service (DTS) from .NET

Frequently, Database administrators import, export and transform data like support for consolidation and data analysis tasks, fundamentally for the development of applications and updates of Databases or Servers.

Most of the organizations have different formats and locations for data storing and frequently it is precise to move the data from a location to another one. A SQL Server DTS provide the tools necessary to extract, to transform and to consolidate the different data of sources to unique or multiple destinies. These tools of DTS allow the creation of solutions for the movement of customized data that adjust to the special necessities of an organization.

The .NET Framework offers the possibility of Execute SQL Server DTS in a simple way. The only thing that is required is to make a reference to the DTS DLL and begin to codify a pair of lines.

References:

SQL Server 2005: C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTS.dll

SQL Server 2000: C:\Program Files\Microsoft SQL Server\80\Tools\Binn\DTSPkg.dll

Code:

VB.NET
Dim package As New DTS.Package2Class ‘(or DTS.Package)
package.LoadFromSQLServer(“ServerName”, “ServerUserName”, “ServerPassword”, , , , , “PackageName”)
package.Execute()

C#.NET
DTS.Package package = new DTS.Package();
package.LoadFromSQLServer(“ServerName”, “ServerUserName”, “ServerPassword”, , , , , “PackageName”);
package.Execute();

If you need to access to a SQl Server Global Variable you can do the following:

VB.NET
Dim MyGlobalVar As String = pkg.GlobalVariables.Item("MyGlobalVar").Value.ToString()

C#.NET
String MyGlobalVar = pkg.GlobalVariables.Item("MyGlobalVar").Value.ToString();