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.
