The Data Transformation Services (DTS) within SQL Server help to solve business problems by providing graphical tools and COM objects that can be used to extract, transform, and consolidate data from different sources. This article will give you a brief overview of DTS, and show you how to create and execute packages within Active Server Pages (ASP).
The book definition, "DTS provides functionality to import, export, and transform data utilizing COM, OLE DB, and Active X Script. This can be accomplished by using the import/export wizard, DTS Designer, or programming."
My definition, "A very cool tool that saves hours of time importing and exporting data from SQL Server. It offers heterogeneous data movement, validation, and a transaction based workflow engine."
The code below assumes that you have a basic understanding of DTS and COM. You can find detailed documentation and tons of samples on the SQL Server 2000 and SQL Server 7.0 books on-line. If you cannot find enough love within the Microsoft documentation, e-mail me at cwilson@harborviewsolutions.com.
Less talk and more programming!
Before we start programming, the following items should be checked.
Make sure that you register the dtspkg.dll on your web server. The path for SQL2K is C:\Program Files\Microsoft SQL Server\80\tools\binn\dtspkg.dll. You can register the DLL by running regsvr32.exe.
Add a reference to the type library for the dtspkg.dll file. For example, <!--METADATA TYPE="TypeLib" NAME="Microsoft DTSPackage Object Library" UUID="{10010001-EB1C-11CF-AE6E-00AA004A34D5}" VERSION="2.0"-->
Packages
A package can consist of connections, tasks, and workflow objects that can be saved to a SQL Server, Visual Basic file or structured storage file. A package can contain one or more steps and tasks.
Below are some of the most common connections, tasks, and workflow objects.
Connections - Any OLEDB database, Access, Excel, Dbase, Paradox, OBDC, and text files. Tasks - FTP, Execute SQL, Active X Script, Transform data, execute process, send mail, copy database objects, and bulk insert. Workflow - on success, on failure, on completion
Executing packages stored within SQL Server
<%
Dim objPkg, strError, iCount
'Create and Execute the package
Set objPkg = Server.CreateObject("DTS.Package")
objPkg.LoadFromSQLServer "<SERVER NAME>", "<USER NAME>", "<PASSWORD>", _
DTSSQLStgFlag_Default,"","","","<PACKAGE NAME>"
objPkg.Execute
'Check For Errors
For iCount = 1 To objPkg.Steps.Count
If objPkg.Steps(iCount).ExecutionResult = DTSStepExecResult_Failure Then
strError = strError + objPkg.Steps(iCount).Name + " failed. " + chr(13)
End IfNextIf strError = "" Then
Response.Write "Success"
Else
Response.Write strError
End IfSet objPkg = Nothing
%>
Please make sure to replace <server name>, <user name>, <password>, and <package name> with the proper information. An important item to consider is the security flag value; it can be DTSSQLStgFlag or DTSSQLStgFlag_UseTrustedConnection. This setting informs SQL Server of your desired authentication type.
The DTSSQLStgFlag is kind of misleading, if you use Windows authentication vice SQL Server authentication for your connection object within your package, the IUSR_<WEBSERVER> account will be used to execute the package. If IUSR_<WEBSERVER> does not have the proper rights on the server, the package execution will fail.
You can use the DTSSQLStgFlag_UseTrustedConnection flag; however, even if you give it a valid NT user name and password, it will still use the IUSR_<WEBSERVER> account. You can confirm this by running a trace with SQL Profiler.
In short, if you plan to execute packages from the web, make sure you use SQL Server authentication within your package and the DTSSQLStgFlag within your ASP code.
Executing packages stored within a structured storage file
<%
Dim objPkg, strError, iCount
'Create and Execute the package
Set objPkg = Server.CreateObject("DTS.Package")
objPkg.LoadFromStorageFile "C:\test.dts","","","",""
objPkg.Execute
'Check For Errors
For iCount = 1 To objPkg.Steps.Count
If objPkg.Steps(iCount).ExecutionResult = DTSStepExecResult_Failure Then
strError = strError + objPkg.Steps(iCount).Name + " failed. " + chr(13)
End IfNextIf strError = "" Then
Response.Write "Success"
Else
Response.Write strError
End IfSet objPkg = Nothing
%>
This example does not use a password on the storage file. I do not recommend doing this within a production environment.
Creating a simple DTS package
This example uses the NORTHWIND and PUBS databases. You can easily change the code to work with any two databases on your server. It will transfer a table from NORTHWIND database to the PUBS database.
<%
Dim oPkg,oConn,oStep,oTask,oCustomTask
Set oPkg = Server.CreateObject("DTS.Package2")
Set oStep = oPkg.Steps.New
Set oTask = oPkg.Tasks.New("DTSTransferObjectsTask")
Set oCustomTask = oTask.CustomTask
'Fail the package on any error
oPkg.FailOnError = False
'Task Information
oTask.Name = "Task 1"
'Set step information
oStep.Name = "Transfer Data"
'specifies whether the steps execute in the main
'thread of the DTS package or a worker thread
oStep.ExecuteInMainThread = True
'Set information for the Transfer Object Task
With oCustomTask
.Name = "Transfer Objects"
'Source Information
.SourceServer = "<SERVER NAME>"
.SourceUseTrustedConnection = False
.SourceDatabase = "Northwind"
.SourceLogin = "<USER NAME>"
.SourcePassword = "<PASSWORD>"
'Destination Information
.DestinationServer = "<SERVER NAME>"
.DestinationUseTrustedConnection = False
.DestinationDatabase = "pubs"
.DestinationLogin = "<USER NAME>"
.DestinationPassword = "<PASSWORD>"
.CopyAllObjects = False
.IncludeDependencies = False
.IncludeLogins = False
.IncludeUsers = False
.DropDestinationObjectsFirst = True
.CopySchema = True
.CopyData = DTSTransfer_AppendData
.AddObjectForTransfer "customers", "dbo", DTSSQLObj_UserTable
End With
'Associate the step with the task
oStep.TaskName = oCustomTask.Name
'Add the step
oPkg.Steps.Add oStep
'Add the task
oPkg.Tasks.Add oTask
'Run the package and release references.
oPkg.Execute
'Check For Errors
For iCount = 1 To oPkg.Steps.Count
If oPkg.Steps(iCount).ExecutionResult = DTSStepExecResult_Failure Then
strError = strError + oPkg.Steps(iCount).Name + " failed. " + chr(13)
End IfNextIf strError = "" Then
Response.Write "Success"
Else
Response.Write strError
End If
'Clean up
Set oCustomTask = NothingSet oTask = NothingSet oStep = Nothingset opkg = Nothing
%>
Summary
This article gave you a brief introduction into programming DTS packages from ASP. We will continue to separate DTS into manageable chunks and construct ASP code within future articles. If you have any questions, comments, or problems with the code please do not hesitate to send me an e-mail.
If you'd like a copy of the file listings above, you can download them from here.
