Control Tasks
P
ART
IV
426
The Execute Process task is one of the least complex of all the DTS tasks. Its only purpose is
to run an executable program or a batch file.
When to Use the Execute Process Task
The importance of the Execute Process task is in the way it integrates DTS packages with
other applications and batch processes. Many companies have existing programs that transfer
data. You can use DTS as a control panel to run all of your data transformation applications.
You can use the various DTS tasks when you want to manipulate data in a new way. You can
use the Execute Process task to coordinate your existing data manipulation applications with
the rest of what you are doing with DTS.
Consider the following specific ways of using the Execute Process task.
Bulk Copying from SQL Server to a Text File
If you are creating a new bulk copy operation to load SQL Server, I suggest that you use the
Bulk Insert task. But you can’t use it if you want to bulk copy data out of SQL Server.
However, you can integrate that bulk copy into DTS by using the Execute SQL task. Use the
bcp command-line utility to do the bulk copying.
Here’s a sample of how to do that. Use the following values in the Execute Process Task
dialog:
•Win32 Process—
bcp
•Parameters—
out “SELECT au_lname, au_fname as FullName FROM pubs..authors
ORDER BY au_lname” queryout C:\Authors.txt -c -S(local)-T
Figure 22.1 shows the Execute Process Task Properties dialog with the properties set to execute
this bulk copy.
F
IGURE

mand-line utility:
DTSRun /E /N PackageName /F c:\temp\LoadEmployee.dts
The Execute Process Task
C
HAPTER
22
22
T
HE
E
XECUTE
P
ROCESS
T
ASK
427
You have greater control in executing one package from another when you use the
Execute Package task.
N
OTE
27 0672320118 CH22 11/13/00 4:56 PM Page 427
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Creating the Task and Setting Its Properties
You can create the Execute Process task in the DTS Designer or in code. Neither of the DTS
wizards creates an Execute Process task.
The Execute Process Task Properties dialog has only one tab. The dialog lets you set five of the
task’s seven properties. You have to use code or Disconnected Edit to view or modify the
Name
and
FailPackageOnTimeout

or not you terminate the application on timeout, the DTS package will continue its exe-
cution. The default value for this property is
FALSE
.
•
FailPackageOnTimeout
—This property causes the whole DTS package to be terminated
if a timeout occurs in the Execute Process task. This value cannot be set or viewed in the
dialog. The default value is
FALSE
.
The
GetExpandedProcessCommandLine
Method of the
CreateProcess2
Object
In code, the Execute Process task is implemented by the
CreateProcess2
object. This object
inherits all the properties of the
CreateProcess
object.
Control Tasks
P
ART
IV
428
27 0672320118 CH22 11/13/00 4:56 PM Page 428
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The

ISTING
22.1
The Visual Basic Code to Create an Execute Process Task
Option Explicit
Public Function fctCreateExecuteProcessTask( _
pkg As DTS.Package2, _
Optional sBaseName As String = “ExecuteProcessTest”, _
Optional sProcessCommandLine As String = “”, _
Optional lSuccessReturnCode As Long = 0, _
Optional lTimeout As Long = 0, _
Optional bTerminateProcessAfterTimeout As Boolean = False, _
Optional bFailPackageOnTimeout As Boolean = False)
On Error GoTo ProcErr
Dim stp As DTS.Step2
Dim tsk As DTS.Task
Dim cus As DTS.CreateProcessTask2
The Execute Process Task
C
HAPTER
22
22
T
HE
E
XECUTE
P
ROCESS
T
ASK
429

Exit Function
ProcErr:
MsgBox Err.Number & “ - “ & Err.Description
fctCreateExecuteProcessTask = “”
GoTo ProcExit
End Function
Control Tasks
P
ART
IV
430
L
ISTING
22.1
Continued
27 0672320118 CH22 11/13/00 4:56 PM Page 430
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Conclusion
The Execute Process task is simple, but it is a key player in the integration of DTS with your
other data manipulation applications.
This is the last chapter discussing the DTS tasks. Part V continues with a discussion of the
DTS package as a whole and the steps that control the flow of the tasks.
The Execute Process Task
C
HAPTER
22
22
T
HE
E

The DTS Package and Its
Properties
IN THIS CHAPTER
•Identifying DTS Packages 436
•Storing DTS Packages 438
• Encrypting DTS Packages 444
• Retrieving Information About Packages 445
• Package Logs and Error Files 451
• DTS Packages as Data Sources 460
•Other DTS Package Object Properties and
Methods 465
29 0672320118 CH23 11/13/00 5:02 PM Page 435
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
DTS Packages and Steps
P
ART
V
436
The package is at the highest level in the DTS object hierarchy. You can’t create tasks, steps,
connections, or global variables outside of a package. It’s the DTS level that is used for saving,
loading, and executing a particular set of DTS steps that have their associated tasks.
As with many of the DTS tasks, the DTS
Package
object has a new implementation in SQL
Server 2000 as the
Package2
object. This object inherits the properties and methods of the
Package object
and adds several new ones.
This chapter describes several facets of the DTS package as a whole. See Chapter 24, “Steps

Packages and their versions are identified by 16-byte globally unique identifiers (GUIDs). The
Package GUID and Version GUID are displayed on the General tab of the DTS Package
Properties dialog (see Figure 23.2).
The DTS Package and Its Properties
C
HAPTER
23
23
T
HE
DTS
P
ACKAGE AND
I
TS
P
ROPERTIES
437
When you choose a particular version of a DTS package for editing and then save
your changes, you don’t overwrite the previous version. Instead, a new version is
saved. The only way you can get rid of previous versions is by explicitly deleting them.
N
OTE
F
IGURE
23.2
The DTS Package Properties dialog displays all the identification information for a package.
When a package is first created, the two GUID values will be the same. When later versions
are created, the Package GUID remains the same and the Version GUID is always changed.
Either the Package GUID or the Version GUID can be used to identify a package for retrieval

You can save, retrieve, and delete packages in the different storage locations by using methods
of the
Package
object. There is also one method,
SaveAs
,that is not implemented in the inter-
face but can be used programmatically. This method saves a package with a new name but
does not store this new package in any form of persistent storage. The
SaveAs
method has one
parameter—
NewName
.
Saving DTS Packages to SQL Server
The definition of a package saved to SQL Server is stored in the sysdtspackages table in the
msdb system database. The image data type is used to save the package.
29 0672320118 CH23 11/13/00 5:02 PM Page 438
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Here are the details on saving and retrieving packages when saving to SQL Server:
•Packages saved to one instance of SQL Server must have unique names.
•You can assign a User password, an Owner password, or both to the package.
•Users must have permission to access the msdb database to save or retrieve the DTS
package.
DTS packages are saved to the SQL Server with the
SaveToSQLServer
method of the
Package
object.
SaveToSQLServer
has the following parameters:

C
HAPTER
23
23
T
HE
DTS
P
ACKAGE AND
I
TS
P
ROPERTIES
439
The save and load methods of the package object both have a parameter called
pVarPersistStgOfHost
, which is a pointer to the screen layout of the package as it
appears in the Package Designer. This pointer cannot be used when saving packages
from VBScript, Visual Basic, or Visual C++. It is only available for internal use by the
Package Designer.
The visual representation of a package is always lost when you save a package pro-
grammatically. That’s unfortunate if you’ve gone to a lot of work making all the tasks
and precedence constraints appear in a logical arrangement. The next time you load
the package, the Package Designer will apply the default arrangement, which often
is not very visually appealing—especially if there are a lot of tasks.
There are hints that Microsoft might give developers the ability to save and re-create
the visual representation in the future. For now, don’t spend too much time making
your packages look nice if you’re planning on saving them programmatically.
N
OTE

RemoveFromSQLServer
method is used to delete a package from SQL Server storage. Their
parameters are similar to those used by the saving methods:
•
ServerName
•
ServerUserName
•
ServerPassword
•
Flags
—Optional parameter. Uses the constants in Table 23.1
•
PackagePassword
—Not used for
RemoveFromSQLServer
.
•
PackageGUID
—Optional. Not needed if a
PackageVersionGUID
or a
PackageName
is pro-
vided.
•
PackageVersionGUID
—Optional. If the
PackageVersionGUID
is not provided, the most

‘Save to the local server
‘Use integrated security
pkg.SaveToSQLServer “(local)”, , , DTSSQLStgFlag_UseTrustedConnection
pkg.LoadFromSQLServer “(local)”, , , _
DTSSQLStgFlag_UseTrustedConnection, , , _
, “Test Save And Retrieve Methods”
FirstPackageID = pkg.PackageID
pkg.Description = “Description to be saved in second package.”
pkg.SaveToSQLServerAs “Renamed Package”, “(local)”, , , _
DTSSQLStgFlag_UseTrustedConnection
pkg.RemoveFromSQLServer “(local)”, , , _
DTSSQLStgFlag_UseTrustedConnection, _
FirstPackageID
Set pkg = Nothing
End Sub
Saving DTS Packages in Meta Data Services
Microsoft Meta Data Services provides a standard method for different products to share infor-
mation. The package’s characteristics are available through the interfaces provided by the
repository’s information models. See Chapter 29, “Integrating DTS with Meta Data Services.”
Here are the details on saving and retrieving packages when using the repository:
•As with packages saved to SQL Server, packages saved to a single instance of Meta Data
Services must have unique names.
• When saving to Meta Data Services, there is a button on the Save DTS Package dialog to
bring up the Scanning Options dialog. These options are also discussed in Chapter 29.
• DTS package encryption is not available for packages saved to Meta Data Services.
• By default, Meta Data Services is located in the SQL Server msdb database. If you cre-
ate a package using Visual Basic, you can specify a different Meta Data Services data-
base.
•Users must have permission to access the database that is hosting the instance of Meta
Data Services that is being used.

RemoveFromRepository
Here are the parameters of the
SaveToRepository
method:
•
RepositoryServerName
—The server where this instance of Meta Data Services is stored.
•
RepositoryDatabaseName
—The database where this instance of Meta Data Services is
located.
•
RepositoryUserName
—The logon name for the server specified in
RepositoryServerName
.
•
RepositoryUserPassword
—The password for the
RepositoryUserName
logon.
•
Flags
—Security choice. Same as
SaveToSQLServer
,except the constants used are
DTSReposFlag_Default
and
DTSReposFlag_UseTrustedConnection
.

• If you want the package to be encrypted, you can provide an Owner password, a User
Password, or both.
•To use a package stored in a file, a user must have the appropriate file system
permissions.
There is no method to delete a package or a version of a package from a file. The other meth-
ods are similar to the SQL Server and Meta Data Services options:
•
SaveToStorageFile
•
SaveToStorageFileAs
•
LoadFromStorageFile
SaveToStorageFile
has fewer parameters than the comparable methods for the other storage
types:
•
UNCFile
—File name to be used in saving the package. Microsoft recommends using a
Uniform Naming Convention (UNC) filename, but that is not required.
•
OwnerPassword
— Password needed to view or edit package structure.
•
OperatorPassword
—Password needed to execute package.
•
pVarPersistStgOfHost
—Pointer to the screen layout information for the package.
•
bReusePasswords

• It gives you a starting point for creating, modifying, and executing DTS packages from
Visual Basic.
• It can help you understand the programmatic structure of your package.
The structure of the Visual Basic file that Save to VB generates is discussed in Chapter 26,
“Managing Packages with Visual Basic and Stored Procedures.”
DTS Packages and Steps
P
ART
V
444
If you are developing a package with the Package Designer and choose Save To VB,
the package will be saved to the code file. If that file already exists, you will receive a
message asking if you want to overwrite the previous file.
The next time you select the Save button, nothing happens. For the other three sav-
ing choices, a new version is created. But when you save to VB, the Save button does
not work. It appears that the Save operation is completed successfully. No error is
returned. But the current version of the package is not saved at all.
You have to select Save As, and if you then select Save to VB or any of the other
choices, your package will again be saved.
C
AUTION
Save to VB is a new feature in SQL Server 2000. It is based on a utility called
ScriptPkg.exe that was included on the installation CD with SQL Server 7.0.
N
OTE
Encrypting DTS Packages
DTS package security differs depending on the package storage location. A DTS package
saved to SQL Server or to a file can be given an Owner password, a User password, or both
passwords. If one or both of the passwords is assigned, the package is encrypted. The encryp-
tion includes all the objects, collections, and properties in the packages except for

• The next section in this chapter, “Package Logs and Error Files,” describes how to
retrieve package logging information.
• Chapter 29, “Integrating DTS with Meta Data Services,” describes how to retrieve lin-
eage information.
• This section discusses how you can use the
Application
object to retrieve information
about SQL Server and Meta Data Services packages.
The
SavedPackageInfos
collection of the
Package2
object is used to retrieve information
about the packages stored in a particular storage file. Since saving a package to VB creates a
separate file with no versioning, there are no comparable strategies for packages saved in
that way.
Package Stored in SQL Server
By using the
Application
object, you can gain a reference to the
PackageInfo
object for each
version of every package stored on a particular SQL Server. The
PackageInfo
object contains
this subset of the
Package2
object’s properties:
•
Name