Other Data Movement and Manipulation Tasks
P
ART
III
326
The File Transfer Protocol (FTP) task allows you to retrieve files from network or Internet
sites.
The lack of FTP integration was a weakness of DTS in SQL Server 7.0. It didn’t prevent
DTS from being used, but it reduced the convenience of the DTS development envi-
ronment. You had to coordinate your FTP processes with the execution of your DTS
packages, ensuring that the files were moved to the proper directories before the
DTS package execution began.
Now, with SQL Server 2000, FTP has become another step in the DTS process.
N
OTE
When to Use the File Transfer Protocol (FTP) Task
The FTP task can be used to move files or directories in your local network. It can also be
used to get files from remote Internet sites. It cannot be used to send files to Internet sites.
It would be more convenient if the FTP task could be used to send files to Internet
sites as well as getting files. There are tools that make an FTP site appear like a
Windows directory. One of these tool, WebDrive (
www.webdrive.com
) is included on
the book’s CD. With WebDrive or a similar tool you can use the FTP task to send files
to remote Internet sites.
T
IP
When you’re using the FTP task, it’s important to recognize the possibilities for integration
with the Dynamic Properties task and with the many ways you can use global variables. You
can use the Dynamic Properties task to change the source, the destination, and the files
involved with the FTP. You can set these values with global variables that have themselves

IGURE
14.1
The FTP task allows you to move files from a local network or from an Internet site.
The primary choice you make on the FTP Site tab is the type of source location. You can
choose either an Internet site or a network directory. This choice is implemented in code by
setting the
SourceLocation
property to one of the
DTSFTPSourceLocation
constants:
•0—
DTSFTPSourceLocation_InternetSite
(The default choice)
•1—
DTSFTPSourceLocation_Directory
Most of the other properties you set on the FTP Site fill in the details about the source of the
FTP transfer:
•
SourceSite
—Must be used for an Internet source. The property is set to an FTP site
name, such as
ftp.mcp.com
.
•
SourceUserName
—“Anonymous” is used as the default, which is the standard username
for making a read-only connection to an Internet FTP site.
18 0672320118 CH14 11/13/00 5:01 PM Page 327
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
•

➥’4312’;’FileThre.dat’;’ftp.mcp.com’;’314’;”
If you specify the site in the
SourceSite
property, you do not need to include the second para-
meter. Also, when you are creating this task programmatically, you do not need to specify the
size of the file. You do still need to include the spaces for these values, however.
18 0672320118 CH14 11/13/00 5:01 PM Page 328
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
This string is equivalent to the preceding one if
ftp.mcp.com
is assigned to the
SourceSite
property:
“‘FileOne.dat’;’’;’’;’FileTwo.dat’;’’;’’;’FileThre.dat’;’’;’’;”
The only other choice you have on the FTP Transformation tab is a check box for specifying
whether or not files with the same name should be overwritten. This choice is implemented
with the
NonOverwritable
property. This property has a default value of
TRUE
, which means
that files are not overwritten.
Creating the Task in Visual Basic
I have created a Visual Basic procedure,
fctCreateFTPTask
, which creates a step, a task, and a
custom task for an FTP task. All the properties of the task can be set with this procedure. You
can find the code for it in the directory for Chapter 14 on the book’s CD as a Visual Basic
Project, with files CreateFTPTask.vbp, CreateFTPTask.frm, and CreateFTPTask.bas.
The code for

T
HE
F
ILE
T
RANSFER
P
ROTOCOL
(FTP)
T
ASK
329
18 0672320118 CH14 11/13/00 5:01 PM Page 329
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
‘Check to see if the selected Base name is unique
sBaseName = fctFindUniqueBaseName(pkg, sBaseName)
‘Create task and custom task
Set tsk = pkg.Tasks.New(“DTSFTPTask”)
Set cus = tsk.CustomTask
With cus
.Name = “tsk” & sBaseName
.Description = sBaseName
.NonOverwritable = bNonOverwritable
If sDestSite <> “” Then
.DestSite = sDestSite
End If
If sSourceFileName <> “” Then
.SourceFilename = sSourceFileName
End If
If sSourceSite <> “” Then

.TaskName = tsk.Name
End With
pkg.Steps.Add stp
fctCreateFTPTask = stp.Name
Set tsk = Nothing
Set cus = Nothing
Set stp = Nothing
ProcExit:
Exit Function
ProcErr:
MsgBox Err.Number & “ - “ & Err.Description
fctCreateFTPTask = “”
GoTo ProcExit
End Function
Conclusion
The FTP task is a helpful addition to DTS in SQL Server 2000. This task furthers the goal of
creating an integrated, RAD environment for data movement and manipulation.
The File Transfer Protocol (FTP) Task
C
HAPTER
14
14
T
HE
F
ILE
T
RANSFER
P
ROTOCOL

Other Data Movement and Manipulation Tasks
P
ART
III
334
The five tasks described in this chapter are the ones used by the Copy Database Wizard to
move databases and associated meta data from one SQL Server to a separate SQL Server 2000.
It’s important to be able to move meta data along with the transfer of databases. SQL Server
stores most of the meta data needed for database manipulation inside each individual database,
but there is a significant amount of meta data that is stored in the Master and Msdb system
databases.
Centralized meta data storage makes it possible for the meta data to be used by all the data-
bases on a server. But the centralized meta data becomes a problem when you move an indi-
vidual database to a new server. Unless you include all the needed meta data, the database will
not operate properly on its new server.
Each of the four additional transfer tasks involves the movement of a particular kind of data:
• Logins, stored in master
• System stored procedures, stored in master
•Error messages, stored in master
•Jobs, stored in msdb
The most common difficulty I have seen in moving databases is getting all the logins moved
properly. But all the meta data is important. Stored procedures, scheduled jobs, and batch
processes can all fail if the proper meta data is missing.
When to Use the Transfer Databases and Other
Transfer Tasks
The five transfer tasks are designed for two purposes:
• The specific purpose of upgrading a SQL Server 7.0 database to SQL Server 2000.
• The more general purpose of moving a database and associated meta data between data-
base servers.
You can only use databases on SQL Server 7.0 or SQL Server 2000 as the source for these

335
15
T
HE
T
RANSFER
D
ATABASES AND
O
THER
T
RANSFER
T
ASKS
F
IGURE
15.1
All five transfer tasks have the same tabs for entering source and destination server information.
The transfer tasks do not use DTS connections. Instead, the connection information must be
entered for both source and destination in the task’s properties dialog.
19 0672320118 CH15 11/13/00 4:58 PM Page 335
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The Transfer Database Task
The Databases tab of the Transfer Databases dialog, shown in Figure 15.2, is the place where
you choose which databases to copy and which databases to move.
Other Data Movement and Manipulation Tasks
P
ART
III
336

C
HAPTER
15
337
15
T
HE
T
RANSFER
D
ATABASES AND
O
THER
T
RANSFER
T
ASKS
F
IGURE
15.3
You can modify the destination files for the databases while you are creating the task.
The Transfer Logins Task
You have two basic options on the Logins tab of the Transfer Logins dialog:
•Include all server logins detected at package runtime.
•Include logins for selected databases.
The other meta data transfer tasks have similar options.
If you choose to include logins for selected databases only, the list of choices is enabled, as
shown in Figure 15.4.
When you’re transferring databases, it’s sometimes reasonable to include only the
logins that are being used in those databases. After all, if certain logins aren’t using

If you want to view details of the jobs you are considering transferring, they are listed in the
Enterprise Manager tree under the Management\SQL Server Agent node.
The Transfer Master Stored Procedures Task
Figure 15.6 shows the Stored Procedures tab of the Transfer Master Stored Procedures dialog
after the choice has been made to select individual stored procedures.
The Transfer Databases and Other Transfer Tasks
C
HAPTER
15
339
15
T
HE
T
RANSFER
D
ATABASES AND
O
THER
T
RANSFER
T
ASKS
F
IGURE
15.6
You choose which of the Master database’s stored procedures to transfer.
If you create a stored procedure in the Master database and it is named with an sp_ prefix, that
stored procedure can be executed from all the databases on a server as if it were local. A stored
procedure created like this is called a system stored procedure.

You can create user-defined error messages to send customized, application-specific error mes-
sages back to client applications. Many developers create a separate set of error messages for
each of their database applications. When used, these messages become an essential part of the
database application.
Moving user-defined error messages from one server to another can be a problem if
the same error number is used for two separate messages. Careful modifications will
have to be made to the code to make sure the wrong error message isn’t called.
I have seen database developers who have used the numbers in the 50000 to 51000
range for their user-defined error messages. Using those numbers brings a high risk
of error number conflicts.
I have seen other developers who use a 4-digit prefix for their database applications,
which is assigned as a unique value for their organization, and another 4-digit value
for each particular error. So altogether, the error numbers all have 8 digits. This sys-
tem reduces the chance of conflict in error numbers.
T
IP
19 0672320118 CH15 11/13/00 4:58 PM Page 340
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Creating the Tasks in Visual Basic
I have created a Visual Basic procedure,
fctCreateTransferDatabaseTask
, which creates a
step, a task, and a custom task for a Transfer Databases task. Only the properties exposed in
Disconnected Edit are set by this procedure.
The Transfer Databases and Other Transfer Tasks
C
HAPTER
15
341
15

The Visual Basic Code to Create a Transfer Databases Task
Public Function fctCreateTransferDatabaseTask( _
pkg As DTS.Package2, _
Optional sBaseName As String = “TransferDatabaseTask” _
) As String
On Error GoTo ProcErr
Dim stp As DTS.Step2
Dim tsk As DTS.Task
Dim cus As OMWTransferDatabases
‘Check to see if the selected Base name is unique
sBaseName = fctFindUniqueBaseName(pkg, sBaseName)
‘Create task and custom task
Set tsk = pkg.Tasks.New(“OMWCustomTasks.OMWTransferDatabases”)
Set cus = tsk.CustomTask
19 0672320118 CH15 11/13/00 4:58 PM Page 341
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
With cus
.Name = “tsk” & sBaseName
.Description = sBaseName
End With
pkg.Tasks.Add tsk
‘Create step for task
Set stp = pkg.Steps.New
With stp
.Name = “stp” & sBaseName
.Description = sBaseName
.TaskName = tsk.Name
End With
pkg.Steps.Add stp
fctCreateTransferDatabaseTask = stp.Name

18 The Execute Package Task 377
19 The Message Queue Task 391
20 The Send Mail Task 405
21 The Analysis Services Tasks 411
22 The Execute Process Task 425
Control Tasks
PART
IV
20 0672320118 PT4 11/13/00 5:01 PM Page 343
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
20 0672320118 PT4 11/13/00 5:01 PM Page 344
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CHAPTER
16
Writing Scripts for an ActiveX
Script Task
IN THIS CHAPTER
• When to Use an ActiveX Script Task 346
•Creating an ActiveX Script Task 346
• Dynamically Modifying DTS Properties 348
•Programming a Loop in a DTS Package 352
• Using ADO Recordsets 355
• Manipulating Files and Directories 357
•Writing Task Log Records 358
• Converting VB Code to VBScript 359
•Creating an ActiveX Script Task in
Visual Basic 361
21 0672320118 CH16 11/13/00 5:01 PM Page 345
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.