DTS Packages and Steps
P
ART
V
526
You can extend the flexibility of DTS by managing packages programmatically. You can man-
age packages with any programming language that supports COM.
This chapter focuses on managing packages with Visual Basic and with the OLE Automation
system stored procedures available in SQL Server. Much of the information in this chapter is
also relevant to other programming languages.
Many of the chapters in this book have examples that show how to use Visual Basic with DTS.
Chapter 12, “The Execute SQL Task,” and Chapter 18, “The Execute Package Task,” have
examples of using the OLE Automation stored procedures. Chapter 30, “Programming with the
DTS Object Model,” has a summary of the programming objects available in DTS. Chapters
31, “Creating a Custom Task with VB,” and 32, “Creating a Custom Transformation with
VC++,” show how to extend DTS functionality by programming custom tasks and custom
transformations.
Working with DTS Packages in Visual Basic
You have many advantages when you use Visual Basic with DTS instead of using the DTS
management tools built into SQL Server 2000:
• The ability to integrate DTS functionality with the rest of your application.
• More flexibility in responding to errors generated by DTS.
•A more convenient development environment for writing programming code.
There are also some disadvantages:
• The DTS Designer and the DTS Wizard generate many DTS objects automatically. A
good deal of programming is needed to re-create these structures using Visual Basic.
• The DTS Designer gives a visual representation of the programmatic flow in the data
transformation. This visual representation is missing when you work with DTS in Visual
Basic.
Because there are distinct advantages to working with DTS in Visual Basic and to working
with the SQL Server 2000 DTS design tools, the best development strategy often uses both.

The DTS Designer and the DTS Wizard both allow you to save a package as a Visual Basic code file.
There are several reasons why you might want to save a package to Visual Basic:
•To search for and replace variable names, DTS object names, data structure names, or
server names throughout a package.
•To verify or modify a particular setting for a property in all objects of a package.
•To merge two or more packages (although you have to avoid conflicts with object names
and connection ID values when you do this).
• So that you can continue development of the package in the Visual Basic development
environment.
•To dynamically modify and execute the package as a part of a Visual Basic application.
•To learn about programming with the DTS object model.
There is one significant problem in saving a package to Visual Basic—you lose the package’s
visual display if you open it up again with the Package Designer.
The DTS Package Designer provides an excellent graphical user interface for displaying the
flow of a data transformation application. You can place connection, task, and workflow icons
in the places that most clearly illustrate what is happening in the data transformation.
32 0672320118 CH26 11/13/00 4:59 PM Page 527
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
This visual display is lost if you save a package to Visual Basic and then execute the Visual
Basic code to save the package to one of the other three forms of storage.
SaveToSQLServer
,
SaveToStorageFile
, and
SaveToRepository
all have a parameter called
pVarPersistStgOfHost
, which is used to store a package’s screen layout information.
Unfortunately, this parameter can only be referenced internally by the DTS Designer. It is not
possible to use this parameter from Visual Basic to save or re-create the package’s visual dis-

ACKAGES WITH
V
ISUAL
B
ASIC
529
If I have a package with a complex layout and I want to make a global change of a
name or a property, I often use an ActiveX Script inside the package to make the
change. The loss of the package’s layout makes me avoid using the Visual Basic saving
option for simple changes in complex packages.
N
OTE
Setting Up the Visual Basic Design Environment
You can create a package in Visual Basic using any one of these project types:
•Standard EXE
• ActiveX EXE
• ActiveX Document EXE
• ActiveX DLL
• ActiveX Document DLL
32 0672320118 CH26 11/13/00 4:59 PM Page 529
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Code Libraries Needed for DTS
You will need to add references to some or all of the following libraries:
•Microsoft DTSPackage Object Library (dtspkg.dll)—Required for all DTS packages.
•Microsoft DTSDataPump Scripting Object Library (dtspump.dll)—Almost always
required. It contains the built-in transformations and the DTS scripting object.
•Microsoft DTS Custom Tasks Object Library (custtask.dll)—Contains the Message
Queue task, the FTP task, and the Dynamic Properties task.
• DTSOLAPProcess (msmdtsp.dll)—Contains the Analysis Services Processing task.
• DTSPrediction (msmdtsm.dll)—Contains the Data Mining Prediction task.

• One function to create each of the tasks
• One function to create each of the transformations in the transformation tasks
Header Information
The Visual Basic code module for a DTS task starts with header information that describes
the creation of the module. The header for the Save To VB Demo Package is shown in
Listing 26.1.
L
ISTING
26.1
The Header for a DTS Visual Basic Code Module
‘****************************************************************
‘Microsoft SQL Server 2000
‘Visual Basic file generated for DTS Package
‘File Name: C:\Temp\Save To VB Demo Package.bas
‘Package Name: Save To VB Demo Package
‘Package Description: Save To VB Demo Package
‘Generated Date: 8/8/2000
‘Generated Time: 8:25:24 AM
‘****************************************************************
Declaration of Public Variables
The code module declares two public variables, one for a
Package
object and the other for a
Package2
object. It’s necessary to have a
Package2
object so that the extended features in SQL
Server 2000 can be accessed. The
Package
object is needed to handle events in Visual Basic.

•Create package steps
•Create package tasks
•Save or execute package
The function begins by setting the properties of the DTS package as a whole, as shown in
Listing 26.3.
L
ISTING
26.3
Setting the Package Properties at the Beginning of the Main Function
Private Sub Main()
set goPackage = goPackageOld
goPackage.Name = “Save To VB Demo Package”
goPackage.Description = “Save To VB Demo Package”
goPackage.WriteCompletionStatusToNTEventLog = False
goPackage.FailOnError = False
goPackage.PackagePriorityClass = 2
goPackage.MaxConcurrentSteps = 4
goPackage.LineageOptions = 0
goPackage.UseTransaction = True
goPackage.TransactionIsolationLevel = 4096
goPackage.AutoCommitTransaction = True
goPackage.RepositoryMetadataOptions = 0
goPackage.UseOLEDBServiceComponents = True
goPackage.LogToSQLServer = False
goPackage.LogServerFlags = 0
goPackage.FailPackageOnLogFailure = False
goPackage.ExplicitGlobalVariables = False
goPackage.PackageType = 0
DTS Packages and Steps
P

oConnection.ConnectionProperties(“Persist Security Info”) = True
oConnection.ConnectionProperties(“Initial Catalog”) = “pubs”
oConnection.ConnectionProperties(“Data Source”) = “(local)”
oConnection.ConnectionProperties(“Application Name”) = _
“DTS Designer”
oConnection.Name = “Pubs Connection”
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = “(local)”
oConnection.ConnectionTimeout = 60
oConnection.Catalog = “pubs”
oConnection.UseTrustedConnection = True
oConnection.UseDSL = False
‘If you have a password for this connection,
‘please uncomment and add your password below.
‘oConnection.Password = “<put the password here>”
goPackage.Connections.Add oConnection
Set oConnection = Nothing
Managing Packages with Visual Basic and Stored Procedures
C
HAPTER
26
26
M
ANAGING
P
ACKAGES WITH
V
ISUAL

Code to Create the DTS Steps
‘------------------------------------------------------
‘ create package steps information
‘------------------------------------------------------
Dim oStep as DTS.Step2
Dim oPrecConstraint as DTS.PrecedenceConstraint
‘------------- a new step defined below
Set oStep = goPackage.Steps.New
oStep.Name = “DTSStep_DTSExecuteSQLTask_1”
oStep.Description = “Update Authors”
oStep.ExecutionStatus = 1
oStep.TaskName = “DTSTask_DTSExecuteSQLTask_1”
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = “VBScript”
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False
goPackage.Steps.Add oStep
Set oStep = Nothing
DTS Packages and Steps
P
ART
V
534

SaveToSQLServer
method with
SaveToFile
or
SaveToRepository
.
L
ISTING
26.7
The Last Section of the Main Function Has Code to Save and/or Execute
the Package
‘goPackage.SaveToSQLServer “(local)”, “sa”, “”
goPackage.Execute
goPackage.Uninitialize
‘to save a package instead of executing it, comment out the _
‘executing package line above and uncomment the saving package line
set goPackage = Nothing
set goPackageOld = Nothing
End Sub
Functions to Create the Tasks
The code module ends with individual functions that create the tasks for the package. The sam-
ple module creates one simple Execute SQL task, as shown in Listing 26.8. Much more code is
needed to create any of the transformation tasks. Separate individual functions are created for
each of the transformations in a transformation task.
Managing Packages with Visual Basic and Stored Procedures
C
HAPTER
26
26
M

oCustomTask1.CommandTimeout = 0
oCustomTask1.OutputAsRecordset = False
goPackage.Tasks.Add oTask
Set oCustomTask1 = Nothing
Set oTask = Nothing
End Sub
DTS Packages and Steps
P
ART
V
536
Most of the code used in the module is needed to correctly create this task. The two
properties that could be omitted are
CommandTimeout
and
OutputAsRecordset
.
N
OTE
Executing a Package from Visual Basic
There are times when it is useful to execute a package from Visual Basic, even if you’re not
interested in saving your packages in Visual Basic code. You can load a package, handle
events, and handle errors without modifying the structure of the package at all. You can also
load a package and modify one or more properties before you execute it. You can create data
transformation loops by executing a DTS package repeatedly.
32 0672320118 CH26 11/13/00 4:59 PM Page 536
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Loading and Saving Packages
You can load a DTS package from or save it to SQL Server storage, Meta Data Services stor-
age, or a structured storage file using the appropriate load and save methods. These methods

‘Save the package
If sUserID = “” Then
pkg.SaveToSQLServer sServer, , , _
DTSSQLStgFlag_UseTrustedConnection
Else
pkg.LoadFromSQLServer sServer, sUserID, sPassword, _
Managing Packages with Visual Basic and Stored Procedures
C
HAPTER
26
26
M
ANAGING
P
ACKAGES WITH
V
ISUAL
B
ASIC
537
32 0672320118 CH26 11/13/00 4:59 PM Page 537
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
DTSSQLStgFlag_Default
End If
End Function
Handling Events
One of the advantages of using Visual Basic to execute a DTS package is the ability to handle
events with Visual Basic code. There are five events returned from DTS, all of which are
declared with the
Package

TRUE
.
OnProgress
and
OnError
have additional parameters that return more detailed information
about the event that has occurred.
Here’s what you have to do in order to handle DTS events in your Visual Basic project:
1. Declare a
Package
object variable using the keyword
WithEvents
. You cannot add events
to a
Package2
object:
Public WithEvents pkg70 As DTS.Package
2. If you are using SQL Server 2000 functionality, you also have to declare a
Package2
object variable and assign it to the
Package
variable. You don’t have to reference the
Package
variable anywhere else in your code:
Public pkg As New DTS.Package
Set pkg70 = New DTS.Package
Set pkg = pkg70
3. Write code for all five of the package events. If you don’t want anything to happen for
one of the events, you can just insert a comment in the error handler. You may trigger an
access violation if you fail to include code for any of the events.

ACKAGES WITH
V
ISUAL
B
ASIC
539
If you save a package to a Visual Basic code module, you have to move the code into
a module that defines classes, such as a form, before you can add events. Note also
that you have to remove the keyword
New
from the declaration of the package vari-
able because you can’t use
New
in a variable declaration when you are using
WithEvents
.
N
OTE
I have written a Visual Basic application called ViewDTSEvents that allows you to look at any
or all of the events generated by a DTS package. You can find the code and the compiled form
of this application on the CD in these files—ViewDTSEvents.vbp, ViewDTSEvents.frm, and
ViewDTSEvents.exe.
ViewDTSEvents lets you execute any DTS package stored in SQL Server storage and choose
which DTS events you want to view. Each event is shown with a message box. For
OnError
and
OnQueryCancel
events, you have the opportunity to cancel the execution of the package.
Listing 26.10 shows the code from ViewDTSEvents that is used to respond to the
OnQueryCancel

event. You handle them with a normal Visual Basic error handler.
You can view errors during the execution of a DTS package in Visual Basic by using the
OnError
event, as described in the preceding section. You can also use the
GetExecutionErrorInfo
method of each of the steps to find the errors that have occurred. The
ViewDTSEvents application uses this method to display a message box for each error that has
taken place, using the code in Listing 26.11.
L
ISTING
26.11
Code to Display All the Errors That Have Occurred in a DTS Package
Private Sub cmdDisplayErrors_Click()
Dim stp As DTS.Step2
Dim lNumber As Long
Dim sSource As String
Dim sDescription As String
Dim sHelpFile As String
Dim lHelpContext As Long
Dim sIDofInterfaceWithError As String
Dim msg As String
Dim bError As Boolean
bError = False
For Each stp In pkg.Steps
If stp.ExecutionStatus = DTSStepExecStat_Completed And _
stp.ExecutionResult = DTSStepExecResult_Failure Then
DTS Packages and Steps
P
ART
V

TRUE
if you want the package to stop its
execution when the first error occurs:
If chkFailOnError Then
pkg.FailOnError = True
End If
Dynamic Modification of Properties
You can dynamically modify the properties of a DTS package from inside that package by
using the Dynamic Properties task or ActiveX Script code. But sometimes it is more conve-
nient to modify properties of the DTS objects using Visual Basic before the package is exe-
cuted. This is especially true when you are receiving some input from a user.
Managing Packages with Visual Basic and Stored Procedures
C
HAPTER
26
26
M
ANAGING
P
ACKAGES WITH
V
ISUAL
B
ASIC
541
L
ISTING
26.11
Continued
32 0672320118 CH26 11/13/00 4:59 PM Page 541

The SQL Namespace Object Library is a set of programming objects that allows you to use the
Enterprise Manager’s interface programmatically. You can use SQL Namespace to execute a
DTS package.
When you use the
Execute
method of the
Package
object in Visual Basic, there is no visual
feedback showing the progress of the package’s execution. If you want a progress report or
notification that the package has finished, you have to provide that information programmati-
cally. When you use SQL Namespace to execute a package, your application uses the
Executing Package dialog (see Figure 26.4) to report on the execution. The report looks the
same as if you had executed the package directly in the Enterprise Manager.
DTS Packages and Steps
P
ART
V
542
32 0672320118 CH26 11/13/00 4:59 PM Page 542
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
F
IGURE
26.4
When you use SQL Namespace, the results of the package execution are shown in the Executing Package dialog.
SQL Namespace executes a package directly from its storage location. You do not load the
package first, as you do when using the
Package
’s
Execute
method. You can only execute

26
26
M
ANAGING
P
ACKAGES WITH
V
ISUAL
B
ASIC
543
32 0672320118 CH26 11/13/00 4:59 PM Page 543
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Optional bSQLServerStorage As Boolean = True) As Long
On Error GoTo ProcErr
Dim sqlns As New SQLNamespace
Dim sqlnsObject As SQLNamespaceObject
Dim hWnd As Long
Dim hServer As Long
Dim hDTSPackages As Long
Dim hPackages As Long
Dim hPackage As Long
Dim sConnection As String
‘Create connection string
If sServer = “” Then
sConnection = “Server=.;” ‘Local server
Else
sConnection = “Server=” & sServer & “;”
End If
If sUser = “” Then

hPackages = SQLNS.GetFirstChildItem( _
hDTS, SQLNSOBJECTTYPE_DTS_REPOSPKGS)
End If
‘Get a reference to the particular package
hPackage = SQLNS.GetFirstChildItem( _
hPackages, SQLNSOBJECTTYPE_DTSPKG, sPackageName)
‘Set the package to be a SQL Namespace object
Set sqlnsObject = SQLNS.GetSQLNamespaceObject(hPackage)
‘Execute the package
sqlnsObject.ExecuteCommandByID _
SQLNS_CmdID_DTS_RUN, hWnd, SQLNamespace_PreferModal
‘Return with no error
fctDTSExecuteSQLNamespace = 0
ProcExit:
Exit Function
ProcErr:
Msgbox Err.Number & “ “ & Err.Description
fctDTSExecuteSQLNamespace = Err.Number
GoTo ProcExit
End Function
Working with Packages Using the OLE
Automation Stored Procedures
SQL Server has a set of OLE Automation system stored procedures that allow you to work
with COM objects. You can load, modify, and execute DTS packages with these stored proce-
dures, but you cannot use them to create new tasks, connections, or steps. You also cannot
respond to events in the DTS package with these stored procedures.
The OLE Automation stored procedures all have an
sp_OA
prefix:
•