SQL Server 2000 DTS Part 4 – DTS Designer Tasks

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.

»


See All Articles by Columnist
Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles