ASP 101 - Active Server Pages 101 - Web06
The Place ASP Developers Go!

Please visit our partners


Windows Technology Windows Technology
15 Seconds
4GuysFromRolla.com
ASP 101
ASP Wire
VB Forums
VB Wire
WinDrivers.com
internet.commerce internet.commerce
Partners & Affiliates














ASP 101 is an
internet.com site
ASP 101 is an internet.com site
IT
Developer
Internet News
Small Business
Personal Technology

Search internet.com
Advertise
Corporate Info
Newsletters
Tech Jobs
E-mail Offers

ASP 101 News Flash ASP 101 News Flash



 Top ASP 101 Stories Top ASP 101 Stories
The Top 10 ASP Links @ Microsoft.com
What is Adovbs.inc and Why Do I Need It?
An Overview of ASP.NET

QUICK TIP:
VBScript's Line Continuation Character
Show All Tips >>
ASP 101 RSS Feed ASP 101 Updates


A Brief Overview of SQL Server's Data Transformation Services

A Brief Overview of SQL Server's Data Transformation Services

by Carvin Wilson

Overview

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.

  1. 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.
  2. 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 If
  Next
  If strError = "" Then
    Response.Write "Success"
  Else
    Response.Write strError
  End If
  Set 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 If
  Next
  If strError = "" Then
    Response.Write "Success"
  Else
    Response.Write strError
  End If
  Set 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 If
   Next
  If strError = "" Then
   Response.Write "Success"
  Else
   Response.Write strError
  End If
'Clean up
Set oCustomTask = Nothing
Set oTask = Nothing
Set oStep = Nothing
set 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.

Happy Coding =)!


Home |  News |  Samples |  Articles |  Lessons |  Resources |  Forum |  Links |  Search |  Feedback

Internet.com
The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers