SQL Server 2000 DTS Part 2 – Creating DTS Packages using Wizards

As we explained in the first article of
this series, there are three methods of creating DTS packages – with DTS
Designer graphical interface, with programming by directly using the DTS object
model, or with wizards available from the SQL Server Enterprise Manager
interface. We will focus here on the third method, which despite being the most
limited, is very straightforward and will serve as an introduction to the
remaining two, more sophisticated methods. Enterprise Manager offers two types
of wizards providing a quick and friendly way of creating DTS packages: DTS
Export/Import Wizard and Copy Database Wizard.

DTS Export/Import Wizard

As the name indicates, the purpose of this wizard is to
perform imports/exports between SQL database and any other OLE DB-compliant
data source. Even though both DTS Import and DTS Export wizards are accessible
from the Tools -> Wizards -> Data Transformation Services menu option,
the quickest way to launch both is from the Import or Export option of the
database context sensitive menu. In either case, the wizard will take you
through the following steps:

  • First you will need to determine the source of the data.
    Depending on your selection, you might have to provide additional
    authentication information. For example, when importing data from another SQL
    Server database, you might be able to use Windows domain accounts, instead of
    SQL Server logins, while selecting Access or Oracle will force you to deal with
    different authentication choices. The choice of data source will also affect
    the available Advanced Connection Properties (which are OLE DB provider
    specific) displayed after clicking the Advanced button on the Choose a Data
    Source page of the wizard. On the next page of the wizard, you will be prompted
    for equivalent configuration options for destination of data transfer (including
    provider type and advanced connection properties).

  • After you specify the source and destination, you will be asked
    to select one of three types of data that will be imported/exported:

    • Content of one or more tables or views – When the destination
      of the import or export is a database, appropriate tables are created if they
      do not exist prior to the transfer. Otherwise, by default, the transfer results
      in new rows being appended to existing data. This and other settings are
      configurable using options displayed on the Column Mappings and Transformations
      dialog box, which can be adjusted separately for each source and destination
      table (or view) pair. For example, you can decide whether to append or delete
      data in the destination table (or drop and recreate it) if it already exists,
      and whether to enable identity insert. In this last case, as an alternative you
      can specify that the identity column should be ignored during transfer
      (although this option is available for any column, not just identity). You can
      also alter T-SQL statement used to create the destination table.
      If you are familiar with VB Scripting and know DTS object model, you can modify
      the default script used during transformation. Note, however, that applying
      such transformation might significantly slow the import/export process with
      larger amounts of data.

    • Results of a T-SQL query (which gives you more selectivity
      than the first option)
      – This simply means that you specify data to be
      transferred using T-SQL statements. Query Builder (which automates the
      generation of T-SQL statements) and Parse (which verifies its syntactical
      correctness) buttons simplify this task. Even though Query Builder has several
      limitations (e.g. inability to specify descending order or apply GROUP BY
      clause), you can easily modify the final T-SQL statement manually. You can also
      create a T-SQL statement with Query Analyzer and either copy and paste it
      directly into the Type SQL Statement page of the DTS Import/Export Wizard or
      save it into .SQL file and open it from the same page after clicking on the
      Browse… button.

    • Individual database objects and data – This allows
      transfer of any objects between two SQL Server databases. It has a number of
      additional options configurable from the Select Object to Copy page of the
      wizard. You can transfer tables, views, stored procedures, rules, defaults,
      user defined data types, user defined functions, users and database roles, and
      all logins including object level permissions for each (you can not, however,
      transfer selectively individual logins). For each table you can specify whether
      you want to copy indexes, triggers, or primary and foreign keys. You can create
      empty target object or, copy data from the source to them. In the second case,
      you can also decide whether you want to append or replace data existing in the
      destination.

  • Regardless of the choice of types of data to be imported/exported,
    the next page of the wizard, called Save, Schedule, and Replicate Package will
    look the same. It gives you the option of running the package immediately or
    scheduling the package for later execution. The second option requires that the
    package be saved. As mentioned in our previous article, you can save the
    package to the SQL Server (more specifically in the sysdtspackages table of the
    MSDB database), to the SQL Server Meta Data Services (Microsoft Repository),
    Structured Storage File (.DTS file), or Visual Basic File (.BAS file). In
    addition, executing scheduled packages requires that SQL Server Agent is
    running.
    You also have an option to use replication to publish destination data (if your
    destination happens to be a SQL Server database). This will trigger the Create
    Publication Wizard right after the Import/Export wizard completes, which, in
    turn, will automatically create a publication based on the destination database.

  • If you decided to save the DTS package, you will be presented
    with an additional page labeled Save DTS Package. The content of this page will
    depend on the intended package storage location (specified on the previous
    page).

If you decided to execute the package immediately, you will be able to track
the progress displayed on the Executing Package dialog box. Each step of the
package will be displayed separately with its corresponding execution status.
Otherwise, the package will appear under the Jobs node of Management->SQL
Server Agent container in the SQL Server Enterprise Manager and will execute
according to the schedule you specified. As we mentioned in the previous
article, use logging when scheduling DTS packages (configurable from the
Logging tab of the DTS Package Properties dialog box), since this greatly
simplifies troubleshooting in case of execution failure.

Copy Database Wizard

Even though this wizard is accessible from Tools -> Wizards ->
Management node (instead of Data Transformation Services), it uses Data
Transformation Services behind the scenes and produces a standard DTS package.
Its primary purpose is the transfer of databases between two SQL Servers. This
is commonly done in a staging environment where a test database needs to be
moved to production, but also comes in handy in case of consolidation, where
databases from multiple servers are moved to a single, more powerful one. Note
that in order for the wizard to complete successfully, a copy of transferred
database cannot exist on a destination server and no users can be connected to
the source database during transfer.

As part of the wizard, you will need to specify the source and target
servers, along with authentication options for each. Once this is done, you
will be able to select the source databases that do not exist at the
destination server and specify whether you want to copy or move them (note that
moving the database will not delete physical files at the source). Next, the
wizard will verify that there are no file name conflicts (i.e. names of
database files at the destination do not match the file names used at the
source) and there is sufficient disk space at the destination. In case of
conflicts or disk space problems, you can modify the destination path and file
name. On the Select Related Object page that follows, you specify whether you
want to copy logins (you can limit them to those that are used by selected
databases only), stored procedures, SQL Server Agent jobs and user-defined
error messages. In each case (except for logins), you can choose individual
objects to be transferred. Finally, on the Schedule DTS Package page, you
decide whether you want to run the package immediately or schedule it. In
either case, the package is saved on the destination server, which allows
re-running it in case of initial execution fails.

Note that even though capabilities of both wizards are relatively limited,
you can always modify a wizard-generated package with DTS Designer (or save it
as a Visual Basic module file and use programming methods to refine it further).
Obviously, this requires that you save such packages prior to completing the
wizard. Editing packages with DTS Designer will be the topic of 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.

Latest Articles