DTS and C#

By Thiru Thangarathinam

Back when Microsoft SQL Server 6.5 was still a hot product, the bulk copy program was the only way to backup or export databases from SQL Server to other data sources, such as other database servers or text files. However, in subsequent versions, such as SQL Server 7.0 and SQL Server 2000, Microsoft has added an enormous amount of features, many of which are aimed at improving SQL Server developer and DBA productivity. One such feature is DTS (Data Transformation Services). By creating DTS packages, we can combine several tasks into one process and use any programming language that supports automation (such as Visual Basic .NET, Visual C#, Managed C++) to execute these packages and monitor their progress for errors, etc.

For the purposes of this article, we will create a DTS package that retrieves data from a text file and loads that data into a SQL Server table. After loading the SQL Server table, it also sends an e-mail to the client application that originally supplied the text file data. After creating the package, we will highlight the steps involved in executing this package from a .NET application.

Application Architecture

As mentioned before, we will consider a data movement scenario where a client application sends all necessary data in a text file, which is then read and loaded into a SQL Server table by a DTS package. The client application initiates this data movement process by placing a text file in a pre-defined directory in the Windows File System. This file is then picked up by the Windows Service that monitors that particular directory for creating new files. When a new file is created in that directory, it is automatically picked up by the Windows Service. Once the Windows Service picks up the created file, it then invokes a DTS package, passing in the name of the file and other related information as arguments. The DTS package basically reads the data in the file and adds it to a specific table in the database. After that, it sends an e-mail to the client application informing it of the details of the package execution. This functionality is illustrated in the following screenshot.



The architecture of this application can be split into the following parts.

  • Client Application:
    The client application creates the text file and places it in the Windows File System. Since the main focus of this article is on DTS Packages and how to invoke them from Visual C#, we will not focus on the client application.
  • Windows File System:
    This is basically a directory in the Windows File System. The client application creates new files and places them in this directory.
  • Windows Service:
    This Windows Service application monitors the directory for creation of new files. It does this by using a FileSystemWatcher component. Once the FileSystemWatcher component is set up to monitor the directory, any time a new file is created, an event is raised and this raised event is captured by the Windows Service application. After receiving the event, the Windows Service application invokes a DTS package named TransformPackage and passes in the name of the newly created file by setting its global variables. Before invoking the DTS package, it also sets up necessary event handlers to enable the executing DTS Package to raise events at different stages of its execution.
  • DTS Package:
    The DTS package reads the tab delimited file and transforms the contents of the file into a table in the SQL Server database. As part of the package execution, an e-mail is also sent to the client application informing the client of the successful execution.

In the next section, we will see how to implement these pieces and glue them together to come up with the application.

Implementation

We will start off discussing the architecture’s implementation by considering the DTS Package that provides the core functionality.

Creation of DTS Package

A DTS package is a set of related objects (connections, tasks, and workflows) that can be used to access, transform, and manipulate data from a wide range of sources including text files and relational databases. DTS was introduced with the release of SQL Server 7.0 and was enhanced in SQL Server 2000 because of its immense popularity. To create DTS packages, we use the DTS designer (which is accessible through SQL Server Enterprise Manager). As mentioned before, the DTS package that we are going to create will be used to transform the contents of the text file into a SQL Server table. Once the transformation is done successfully, the DTS package also sends an email to the client application.

To create a new DTS Package, from the SQL Server Enterprise Manager, right click on the Local Packages node (that is present under the Data Transformation Services node) and select New Package from the context menu. Once the new package is created, add two Microsoft OLE DB Provider for SQL Server Connection objects and a Text File (Source) connection object to the designer. After that, add two ActiveX Script tasks to the designer and name them InitializeConnectionObjects and SendMail. Then right click on the DTS Designer and select Package Properties to define the required global variables. After defining the global variables, the screen should appear as shown below.



Now that we have defined the global variables, we need to add code to initialize the global variables with appropriate values. Modify the code in the InitializeConnectionObjects ActiveX script task as follows:

Function Main()

Dim
objConn1

Dim
objConn2

Dim
objConn3

Set
objConn1 = DTSGlobalVariables.Parent.Connections("Text File

(Source)")

objConn1.DataSource
= DTSGlobalVariables("gFilePath").Value

Set
objConn2 = DTSGlobalVariables.Parent.Connections("Microsoft OLE DB

Provider
for SQL Server Copy")

objConn2.DataSource
= DTSGlobalVariables("gServer").Value

Set
objConn3 = DTSGlobalVariables.Parent.Connections("Microsoft OLE DB

Provider
for SQL Server")

objConn3.DataSource
= DTSGlobalVariables("gServer").Value

Main =
DTSTaskExecResult_Success

End Function

The above lines of code are used to initialize the connection objects using values from the pre-defined global variables. Now modify the code in the SendMail ActiveX Script task to look like the following.

Function Main()

On Error Resume Next

Dim
strEmailBody

Dim objMail

Dim
objResults

Dim
strToMailAddress

strToMailAddress
= DTSGlobalVariables("gToMailAddress").Value

Set objMail
= CreateObject("CDONTS.NewMail")

‘Create the
body of the email

strEmailBody
= "Finance Data has been loaded successfully by

the
Windows Service job," & vbCrLf & _

"Name
of the input file is :" &

DTSGlobalVariables("gFilePath").Value

& vbCrLf & _

"Time
of the load is: " & Time

objMail.To
= strToMailAddress

objMail.Subject
= "Data Load Report"

‘Set the
Importance to High

objMail.Importance
= 2

objMail.Subject
= strEmailBody

objMail.Send

If
Err.count = 0 Then

Main
= DTSTaskExecResult_Success

Else

Main
= DTSTaskExecResult_Failure

End If

End Function

As the name suggests, the SendMail task is used to send mail to the clients informing them of the successful execution of the package. To send mails, we use the properties and methods of the CDONTS object.

The next step is to connect the Text File (Source) and the Microsoft OLE DB Provider Connection objects together by using a Transformation Data Task. During this step, you need to map the source columns in the text file to the destination columns in the SQL Server table using the Properties Window of the Transform Data Task step. After the mapping is performed, the Properties window of the Transform Data Task will look similar to the following screenshot.



The Transformations tab in the Properties of the Transform Data Task provides information on how the columns in the source text file are mapped to the columns in the destination SQL Server table. For reasons of brevity, the destination table in the SQL Server database contains only two columns named FirstCol, and SecondCol. Once the DTS package is created, you will see the following output when you bring up the package in the DTS Package designer.



Implementation of Windows Service Application

In this section, we will understand the code required for implementing the Windows Service application. As mentioned before, the Windows Service is used to execute the DTS package (TransformPackage) whenever a new file is placed in the file system directory. To accomplish this functionality, a FileSystemWatcher component is used in the Windows Service application to monitor the file system directory for the creation of new files.

Executing the DTS Package

Executing a DTS package is very simple; in its simplest form the execution contains three steps. These three steps form the foundation for more complicated forms for package execution. They are:

  • Load the package
  • Execute the package
  • Unload the package

In the first step you need to determine from which location you are going to load the package. DTS packages can be stored in three different locations/formats.

  • A file, which most of the time will have the extension “.DTS”, but this is not required. Internally the file is a COM structured storage file, which is capable of hosting multiple packages and multiple versions of the same package inside the file. To execute a package that is stored in the file system, you need to use the LoadFromStorageFile method of the Package class.
  • The package can be stored in the SQL Server msdb database. Packages are stored in the msdb.dbo.sysdtspackages table. The package is stored in the packagedata column; this column contains a single version of the package. When packages are stored in the sysdtspackages table, versions are represented by multiple rows in the table. The contents of the package are a serialized format of the file format, stored as an image data type. To execute a package that is stored in SQL Server, you need to use the LoadFromSQLServer method of the Package class.
  • The last location/format that can be used for storing DTS packages is the Microsoft Meta Data Services, also known as the Microsoft Repository. When stored inside the Meta Data Services, the package is stored as object entities and object relationships. To execute a package that is stored in the repository, you need to use the LoadFromRepository method of the Package class.

After the package has been loaded, executing the package is as simple as calling the Execute method on the Package object, which does not take any parameters. The Execute method is a blocking/synchronous call. If you need progress notifications, etc., you need to use events.

After the Execute package call returns, the last step is to clean up the execution state by calling the UnInitialize method. Until you have called the UnInitialize method you can inspect the execution result of each step in the package and retrieve error information.

Creating a RCW for the DTS COM Component

Since the .NET Framework does not provide a managed implementation of the DTS capabilities, we need to use the existing COM-based DTS component to utilize DTS from within a .NET application. Before you can access the DTS component, you need to create a RCW (Runtime Callable Wrapper) assembly that will be used by the .NET application. To create a RCW assembly, you need to perform the following two steps.

  • Create a RCW assembly using the Type Library Import (tlbimp.exe) utility
  • Register the assembly in the Global Assembly Cache (GAC) utility

The following sections describe the above-mentioned operations in detail.

Creating a RCW Assembly using the Type Library Import Utility

As the name suggests, a Runtime Callable Wrapper assembly is used by .NET managed code to communicate with the unmanaged COM component (In this case, SQL Server DTS component). To be able to interact with DTS packages and perform operations such as execution, creation, and enumeration of packages, you need to interact with the DTS Package COM Component, which is located in “<Drive Name>:\Program Files\Microsoft SQL Server\80\Tools\Binn\dtspkg.dll”.

To invoke this component from within a .NET application, we need to create a RCW assembly using the type library import utility. Before creating the RCW assembly, you need to create a key file using the sn.exe utility that is required for installing the assembly in the GAC. Once the key file is created, we can then associate it with the RCW assembly. Installing the assembly in the GAC allows us to access the RCW assembly from any .NET application in that machine. To create the key file, bring up the Visual Studio .NET command prompt and enter the command as shown in the following screenshot.



Now that the key file is created, we are ready to create the RCW assembly. To create the RCW, use the command shown in the following screenshot.



Once the RCW is created, the next step is to install the assembly in the GAC so that it can be shared by all the applications in that machine.

Installing the Assembly in the GAC (Global Assembly Cache)

In this step, we will install the assembly in the GAC. To accomplish this, bring up the Visual Studio .NET command prompt and specify the command illustrated in the following figure.



Installing the assembly in the GAC facilitates sharing of the assembly across different applications and also obviates the need for creating multiple copies of the same assembly in different applications.

Creating a Windows Service Application that invokes the DTS Package

In this step, we will create a new Windows Service application named WinFileMonitoringService that is basically used to invoke the DTS package at specific time intervals. Once the project is created, we will rename the default class from Service1 to MonitoringService. After that, modify the code in the MonitoringService class to look like the following.

protectedoverridevoid OnStart(string[] args)

{

EventLog.WriteEntry("TestSource","On
Start");

SetupWatcher();

}

In the SetupWatcher method, we set the properties of the FileSystemWatcher component to appropriate values. We also specify that we want to monitor for the creation of new files by hooking up the Created event of the FileSystemWatcher to a public method named OnCreated. By doing this, any time a new file is created in the C:\temp directory, it will automatically invoke the OnCreated method, which processes the newly created files.

privatevoid SetupWatcher()

{

// Create a new
FileSystemWatcher and set its properties.

watcher
= new FileSystemWatcher();

stringdirectoryToMonitor =

ConfigurationSettings.AppSettings["directoryToMonitor"];

watcher.Path
= directoryToMonitor;

//Watch for changes in FileName

watcher.NotifyFilter
= NotifyFilters.FileName;

//Watch for all files.

watcher.Filter
= "";

//Add event handlers.

watcher.Created
+= new FileSystemEventHandler(OnCreated); //Begin
watching

watcher.EnableRaisingEvents
= true;

}

In the OnCreated method, we write an entry to the EventLog, and then we invoke a local private method named ExecutePackage that is basically used to execute the package.

// Define the event handlers.

publicvoid OnCreated(object source, FileSystemEventArgs e)

{

// Specify what is done when a file is created.

EventLog.WriteEntry("TestSource","File:

" + e.FullPath + " " +

e.ChangeType);

//Pass in the path of the file to the DTS Package

ExecutePackage(e.FullPath);

}

The ExecutePackage method is the one that actually executes the DTS package. Before executing the DTS package, it also sets up appropriate event handler interfaces. To properly handle the events raised by the DTS package, we need to implement the IconnectionPointContainer and IconnectionPoint interfaces that are COM-based. The .NET Framework provides managed definitions of these interfaces through the UCOMIConnectionPointContainer and UCOMIConnectionPoint interfaces. These interfaces are part of the System.Runtime.InteropServices namespace. In the ExecutePackage method, we also pass in the instance of class named PackageEventsSink to the Advise method of UCOMIConnectionPoint interface. This step is required for capturing the events raised by the DTS package.

publicvoid ExecutePackage(string fileName)

{

try

{

package
= new PackageClass();

UCOMIConnectionPointContainer
CnnctPtCont =

(UCOMIConnectionPointContainer)
package;

UCOMIConnectionPoint
CnnctPt;

PackageEventsSink
PES = new PackageEventsSink ();

Guid
guid = new Guid("10020605-EB1C-11CF-AE6E-

00AA004A34D5"); // UUID of PackageEvents Interface

CnnctPtCont.FindConnectionPoint(ref guid, out CnnctPt);

int iCookie;

CnnctPt.Advise(PES, out iCookie);

object pVarPersistStgOfHost = null;

//Retrieve global settings from the Configuration File

string serverName =

ConfigurationSettings.AppSettings["serverName"];

string toMailAddress =

ConfigurationSettings.AppSettings["toMailAddress"];

string userName =

ConfigurationSettings.AppSettings["userName"];

string password =

ConfigurationSettings.AppSettings["password"];

string packageName =

ConfigurationSettings.AppSettings["packageName"]; package.LoadFromSQLServer(serverName,userName,
password,

DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, null, null, null, packageName, ref pVarPersistStgOfHost);

//Loop
through all the Global variables and remove the

//variables that are of type string

foreach(GlobalVariable global in

package.GlobalVariables)

{

if (global.Name == "gFilePath")

{

package.GlobalVariables.Remove(global.Name);

}

if (global.Name == "gServer")

{

package.GlobalVariables.Remove(global.Name);

}

if (global.Name == "gToMailAddress")

{

package.GlobalVariables.Remove(global.Name);

}

}

//Read all the global variables that are of type string

package.GlobalVariables.AddGlobalVariable("gFilePath",

fileName); package.GlobalVariables.AddGlobalVariable("gServer",serverName);

package.GlobalVariables.AddGlobalVariable("gToMailAddress",toMailAddress);

package.Execute();

package.UnInitialize();

package
= null;

}

 

catch(System.Runtime.InteropServices.COMException ex)

{

EventLog.WriteEntry("TestSource","Error
Code : " +

ex.ErrorCode + "Error Message :" +
ex.Message + "

Stack
Trace : " + ex.StackTrace);

}

catch(System.Exception ex)

{

EventLog.WriteEntry("TestSource",

"Error Message :" +

ex.Message
+ " Stack Trace : " + ex.StackTrace);

}

}

In the ExecutePackage method, we retrieve a number of global configuration settings from the app.config file, which is defined as follows.

<?xmlversion="1.0"encoding="Windows-1252"?>

<configuration>

<appSettings>

<addkey="toMailAddress"value="thiruthangarathinam@yahoo.com"/>

<addkey="serverName"value="localhost"/>

<addkey="directoryToMonitor"value="C:\Temp"/>

<addkey="userName"value="sa"/>

<addkey="password"value="thiru"/>

<addkey="packageName"value="TransformPackage"/>

</appSettings>

</configuration>

After retrieving the global configuration settings, we then use them to set the values of global variables defined in the DTS package. Note that setting a value of a global variable that is of type string requires you to employ a different approach. Since all the global variables defined in the package are of type string, you need to first remove the global variables from the DTSGlobalVariables collection object. Then you need to set the values of DTS global variables using the AddGlobalVariable method of the DTSGlobalVariables collection object.

protectedoverridevoid OnStop()

{

//Disable the watcher

watcher.EnableRaisingEvents
= false;

EventLog.WriteEntry("TestSource","On
Stop");

}

PackageEventsSink Class

This class is responsible for handling the events raised by the DTS Package. During the execution of the DTS package, different events are fired. For the purposes of this article, we will capture the raised events and write information to the event log for logging purposes.

publicclass PackageEventsSink : PackageEvents

{

publicvoid OnQueryCancel(string EventSource, refbool pbCancel)

{

EventLog.WriteEntry("TestSource","

In OnQueryCancel : Event

Source : " + EventSource);

pbCancel
= false;

}

 

publicvoid OnStart(string EventSource)

{

EventLog.WriteEntry("TestSource","

In OnStart : Event Source

:
" + EventSource);

}

 

publicvoid OnProgress(string EventSource, string ProgressDescription,

int PercentComplete, int ProgressCountLow, int

ProgressCountHigh)

{

EventLog.WriteEntry("TestSource","

In OnProgress : Event

Source : " + EventSource +

"
Progress Description : " + ProgressDescription +

"
PercentComplete : " + PercentComplete +

"

ProgressCountLow : " + ProgressCountLow +

"
ProgressCountHigh : " + ProgressCountHigh);

}

 

publicvoid OnError(string EventSource, int ErrorCode, string Source,

string Description, string HelpFile, int HelpContext, string IDofInterfaceWithError, refbool pbCancel)

{

EventLog.WriteEntry("TestSource","

In OnError : Event Source :

" + EventSource + " Error Code : " +

ErrorCode.ToString() + "
Source : " + Source.ToString()

+ " Description : "

+ Description + " HelpFile : " +

HelpFile + " HelpContext
: " + HelpContext + "

InterfaceError " +
IDofInterfaceWithError);

pbCancel
= false;

}

 

publicvoid OnFinish(string EventSource)

{

EventLog.WriteEntry("TestSource",

"In OnFinish");

}

}

One of the important events raised from the DTS package is OnQueryCancel. This event provides you with an opportunity to cancel the execution of the DTS package. To accomplish this, you need to set the parameter named pbCancel (which is passed by reference to this method) to true. As you can see from the code in the PackageEventsSink class, we also capture an event named OnError. This event is raised if there is an exception during the execution of the DTS package. To this event, details about the actual exception such as EventSource, ErrorCode, Source and Description are passed in the form of parameters.

Putting It All Together

To test the Windows Service application, you need to deploy the Windows Service using a Setup and Deployment Project. You can download the code of this setup project along with the support material for this article. Once you deploy the Windows Service and start the service, the service will start monitoring the directory for the creation of new files. The directory to be monitored is determined by the entry in the app.config file. After that, if you place a new file in the directory, the file will be picked up the Windows service, which will invoke the DTS package. This DTS package will load the contents of the file into a SQL Server table and send an email to the recipient address that is passed from the Windows Service application to the DTS package.

Conclusion

In this article, we discussed how to leverage DTS packages from a .NET application and learned the steps involved in executing a DTS package from a .NET application. While executing a DTS package, we also captured the events raised by the DTS package to monitor the execution of the package.

I hope you find this article useful and thanks for reading.

About the Author

Thiru has many years of experience in architecting, designing, developing and implementing applications using Object Oriented Application development methodologies. He also possesses a thorough understanding of software life cycle (design, development and testing).

He is an expert with ASP.NET, .NET Framework, Visual C#.NET, Visual Basic.NET, ADO.NET, XML Web Services and .NET Remoting and holds MCAD for .NET, MCSD and MCP certifications.

Thiru has authored numerous books and articles. He can be reached at thiruthangarathinam@yahoo.com.

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read