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.
