In the previous article of our series dedicated to SQL Server 2000 Data Transformation
Services, we covered the first of the essential elements of a package –
connection. Now it is time to look into another critical component – task – that
is responsible for the processing of data retrieved through connections. Tasks
are represented by icons in the Task area of the DTS Designer window and can be
added to a package by simply dragging them with the mouse cursor to the package
window area. A number of them will require an existing connection (or
connections – in which case you will be prompted).
There are 17 built-in tasks with varying degrees of complexity in SQL Server
2000 DTS. We will start at the lower end of the spectrum by reviewing 11 of the
more straightforward ones and continue with the rest in our next article:
-
Bulk Insert task – functions as a wrapper for the T-SQL BUILK
INSERT command, so its purpose is to provide a speedy way to import the content
of a delimited text file, (when configuring the task parameters, you can
specify either row and column delimiters or use a format file), into a SQL
Server database table. The main drawback of this task is its lack of
flexibility. In particular, no data transformations can be performed during
bulk import. (If this is a requirement, you might need to resort to the Transform
Data task, which we will discuss later). In addition, the column mapping
feature is not available (which means that fields and text files and columns in
a table need to match), and data transfer can only be performed from a text
file to a SQL Server. You can, however, using the Options page on the Bulk
Insert Task Properties dialog box (accessible by double-clicking the task icon
in the DTS Package window), specify a number of T-SQL BULK INSERT parameters,
such as constraints checking (CHECK_CONSTRAINTS), identity insert settings
(KEEPIDENTITY), rows to be copied (FIRSTROW and LASTROW), type of data file
(DATAFILETYPE), or a batch size (BATCHSIZE). -
Copy SQL Server Objects task – duplicates any set of objects such
as tables (including their data), views, stored procedures, constraints,
triggers, indexes, users and roles, logins, primary and foreign keys, as well
as object level permissions between two SQL Server databases. You should avoid
using this task exclusively for copying data (Transform Data task is more
efficient). In addition, if your intention is to copy an entire database, you
might want to consider utilizing Copy Database Wizard (or Transfer Databases
task, which is discussed later). Even though the wizard requires for a source
database to be offline during its operation (unlike the Copy SQL Server Objects
task), it is superior in terms of speed. -
Execute Process task – runs any Win32 executable or batch file.
For either one, you can specify input parameters, timeout period (after which
the process will be terminated), and return code (integer value indicating
success). The process executes in the security context of the account that
launched the DTS package. -
Execute SQL task – provides the ability to execute SQL statements
(including stored procedures). The statements can be divided into batches
(separated with the GO command). The Execute SQL Task properties dialog box
allows setting the timeout period, after which the corresponding task is forced
to terminate. SQL statement can be parsed for syntactical correctness. While
this capability seems to be rudimentary, it can be significantly enhanced by
employing input/output parameters and global variables in SQL statements.-
Global variables are used to store data, which can then be shared
across multiple components of the same package, or even exchanged among
multiple packages. Since global variables for a package are stored in a single
location and they can be altered dynamically during package execution, they
also simplify package maintenance and customization. We will discuss global
variables in more detail in one of one of the future articles of this series. -
Input parameters appear as question marks in the SQL Statement
portion of Execute SQL Task. Their sequential position determines whether they
are referred to as Parameter 1, Parameter 2, etc. on the Input Parameters tab
of the Parameter Mapping dialog box (accessible by clicking on Parameters
command button in the Execute SQL Task Properties dialog box). Entries in the
Parameter Mapping section (located on the same tab) specify how input
parameters are mapped to global variables. Similarly, on the Output Parameters
tab, you can create a mapping between global variables and output parameters of
the SQL statement. The output parameter can consist of a single row or a rowset
(if your query returns multiple records).
Execute SQL task is frequently applied following
tasks performing data import (e.g. to force rebuilding indexes or updating
statistics). -
-
File Transfer Protocol task – accommodates common requirements in
mixed or distributed environments where data needs to be copied via File
Transfer Protocol. In the previous versions of SQL Server, this had to be
handled via workarounds involving third party programs (or custom, in-house
solutions). SQL Server 2000 DTS has this functionality built-in.File Transfer Protocol task invokes the FTP process
between an Internet (or intranet) site or a server directory and local file
system. Connection parameters (such as username, password and number of
retries) are set from the Location tab of the File Transfer Protocol
Properties dialog box. On the same tab, you need to specify the destination
directory path. Once the existence of the target data store and access rights
to it are verified, you can click on the Files tab within the same dialog box
and specify individual files to be transferred to the destination. By checking
on the Overwrite checkbox, you can also designate whether target files should
be overwritten in case they already exist. -
Send Mail task – allows sending an e-mail as part of a DTS
package. This typically is used to provide confirmation about execution results
for monitoring purposes. Such results can be either specified as part of the
message body or delivered in the form of an e-mail attachment. Before you set
up this task, you first need to install a MAPI compliant client (such as
Outlook) on the system where the package will be executing and configure
appropriately a profile for this client. What tends to be a source of confusion is the fact that such a profile might need to be configured for multiple accounts – depending on how a package is executed.
This might be your account, if you launch packages interactively, but more
frequently it is an account used to run SQL Server Agent service (if packages
are scheduled as jobs). In this case, you need to configure an e-mail profile
after logging on interactively using the SQL Server Agent account, to the
computer on which SQL Server 2000 is installed.You create profiles by double-clicking on the Mail
icon in the Control Panel. Profile parameters are dependent on the type of mail
server (e.g. Microsoft Exchange or Lotus Notes). Once a profile is created, it
will appear in the Send Mail Task Properties dialog box in the Profile name
list box. Within the same dialog box, you will be able to specify message
recipients, message subject and content and optional attachments.Note that the same functionality can be
accomplished with the ActiveX Script task (which we will discuss in our next
article) in combination with methods and properties of the Collaboration Data
Object model. The main benefit of this approach is eliminating the requirement
for a local MAPI profile. -
Transfer Databases task – transfers a database from a SQL Server
7.0 or 2000 to another SQL 2000 Server. You can specify whether a database will
be copied or moved. For destination, you can specify the location of data and
log files (using settings on the File Locations tabs of the Transfer Database
Properties dialog box). You cannot transfer any of the system databases or any
others that already exist at the destination. This task is built into the Copy
Database Wizard, which we have discussed in one of our previous articles. -
Transfer Error Messages task – serves the specialized role of
copying user-defined error messages between two SQL Servers (this includes the
transfer from SQL Server 7.0 to 2000). Such messages are stored in the sysmessages
table in the master database and can be added with the sp_addmessage stored
procedure. You have an option of either copying all custom error messages or
only selected ones. The task will also prevent copying of messages that already
exist at the destination. This task is also part of the Copy Database Wizard. -
Transfer Jobs task – copies jobs defined on a SQL Server 7.0 or
2000 (stored across several tables in the msdb database) to another SQL 2000
Server. This task is configured similarly to the Transfer Error Message task
described above (you can either transfer selected jobs or all of them) and just
like this task, it is included in the Copy Database Wizard. -
Transfer Logins task – transfers logins between SQL Server 7.0 or
2000 and a SQL 2000 Server, in the same manner as the two previously discussed
tasks. As before, you can copy all of them or select them based on databases to
which logins have granted access. This means, you choose a database and all
logins mapped to roles defined in these databases are automatically added. As
with the few most recent predecessors on this list, you have seen this
functionality in the Copy Database Wizard. -
Transfer Master Stored Procedures task – transfers user-defined
stored procedures residing in the master database of a SQL Server 7.0 or 2000
to another SQL Server 2000. Here also you can decide whether to transfer all or
only selected items. As before, in case of naming conflicts, this task will
prevent overwriting existing stored procedures at the destination.
Besides the ones listed above, SQL Server 2000 DTS offers more complex
tasks, such as Execute Package, Dynamic Properties, ActiveX Script, Transform
Data, Data Driven Query, and Message Queue. We will provide their coverage
starting with our next article.
