In the last couple of articles of this series, we have presented a variety
of Control Flow tasks of SQL Server 2005 Integration Services, for which the primary
purpose was the transfer of server and database objects. Despite versatility
intended by their designers, a number of them do not appear to work as
advertised, which clearly limits their usefulness. In particular, Transfer SQL
Server Object proves to be cumbersome and error-prone when copying more complex
collections of interdependent tables between two databases. There are several
techniques of working around these limitations, although they do have their own
drawbacks. We will focus on two of them, implemented in the form of Import and
Export Wizard and Copy Database Wizard.
Import and Export Wizard existed in previous versions of SQL Server (for
more information on its SQL Server 2000 Data Transformation Services based
implementation, refer to our
earlier article). Surprisingly, these versions offer considerably more
functionality than the current one. This might be related to the fact that
Microsoft’s SQL Server 2005 development team intended to move some of its
features into a Transfer Database Wizard. Unfortunately, the new wizard has not
been incorporated into the final product, leaving the new rendition of Import
and Export Wizard without the majority of its more advanced capabilities. Among
the most painfully missing ones is support for transferring objects – other
than tables and views – between two SQL Server databases. However, even with
this unexpected handicap, the Import and Export Wizard will likely remain one
of the primary tools used by database administrators to perform less complex
data transfers and provide the simplest method to create packages handling
these types of tasks.
The Import and Export Wizard can be launched in a few different, but roughly
equivalent ways (although there are some additional caveats associated with
each method that you should be aware of):
-
From the SQL Server Management Studio, you can select Import
Data… or Export Data… items under the Tasks entry in the context sensitive
menu of any non-system database listed in the Object Explorer (note that this
method will not lead to proper execution of the wizard if the SSIS component
has not been installed locally). This approach gives you the flexibility of
performing the transfer immediately, saving its configuration as an SSIS
package (and postponing its execution until later), or both (immediate
execution and save). -
From the Business Intelligence Development Studio, you can select
the SSIS Import and Export Wizard… entry from the context sensitive menu of
the SSIS Packages node in the Solution Explorer or choose the identically
labeled item from the top-level Project menu. In this case, the configuration
produced by the wizard is saved as a new package (within the project, from
which you launched the wizard), and can be subsequently executed. -
From the Command Prompt on the system where SQL Server
Administrative Tools are installed, run DTSWizard.exe. (The executable is
located in the C:Program FilesMicrosoft SQL Server90DTSBinn folder;
however, since this location is included in the PATH environment variable, the operating
system is capable of automatically locating it, regardless of the directory
from which the DTSWizard.exe is invoked). Just as with the wizard launched from
SQL Server Management Studio, this option allows you to immediately run the
resulting package or save it for later execution.
After the initial Welcome page of the wizard, you are prompted to specify
the data source. This involves choosing the appropriate option from the Data
Source list box, which includes such entries as .NET Framework providers for
ODBC, Oracle, and SQL Server, Access, Excel, and flat files (for more
information about this type of transformation refer to an
article by one of our columnists published on the Database Journal Web
site), a number of OLE DB providers (for Analysis Services, Data Mining Services,
OLAP Services, Oracle, SQLXML and SQL Server), and last, but certainly not
least, the SQL Native client. We are interested mainly in this option, since it
is relevant to resolving some of the issues we encountered with previously
discussed SSIS Control Flow transfer tasks. With the exception of .NET
Framework-based providers, the same types of data stores can be chosen as a
destination of the transfer (on the next page of the wizard). Depending on your
choice, you will need to specify additional parameters, such as, for example,
connection properties. In our case, this information will consist of
authentication mechanism (Windows or SQL Server based) and the target database
name (you have an option of creating a new one by clicking on the New… command
button). Subsequently, you will have to decide whether your requirements can be
satisfied by simply copying the entire content of selected tables or views
(note that views are automatically converted to tables in the destination
database) or whether it will be necessary to write a SQL query that will
further customize the import or export operation.
If you choose the first option, you will be presented with the complete list
of tables and views in your source database. You can select each individually, assign
to it destination table or view, and edit column mapping between the two (in the
Column Mappings dialog box, which appears after clicking on the Edit… command
button). You have some degree of flexibility, making it possible to alter
column properties (such as name, data type, its size, or nullability) or delete
(rather than append) rows if the destination table already exists. The "Optimize
for many tables" checkbox is intended to speed up the transfer of larger
numbers of tables (the default threshold that automatically turns this option
on is 20). This is accomplished by dynamically creating a temporary package for
each table or view that is being copied (rather than using one package with a
single Data Flow task that implements data transfer from its source to
destination). There is also a checkbox, which allows you to perform the whole
transfer within an individual transaction.
With the second option, you first need to write a SELECT query, identifying
data to be transferred, in the SQL statement text box on the "Provide a
Source Query" page of the wizard. Once you click on the Next command
button, you will be presented with the Select Source Tables and views page
containing your query, for which you can assign Column Mapping settings (similar
to way this was done with the option described earlier) – allowing for
modifying destination column properties (such as names, data types, their size,
or nullability) as well as deleting or appending rows to the destination table.
Once this process is complete, depending on the way you launched the wizard
(from the SQL Server Management Studio, Business Intelligence Development
Studio, or Command Prompt – as explained before) you will be able to either execute
the package immediately or save it to SQL Server or file system. Your choices
will be confirmed on the Complete the Wizard page and the outcome of the
operation will be displayed in graphical format once the execution completes,
giving you a detailed view of each of its stages. Keep in mind that you need to
be careful when transferring data from interdependent tables. Unfortunately,
the wizard is not smart enough to ensure that parent tables are populated
before their dependents, which might cause foreign key violations.
Note that, regardless of whether the first or second option is used,
advanced copy settings (present in SQL Server 2000 DTS Import and Export
Wizard) are no longer available, which precludes the ability to copy table or view-related
objects. If you are looking into duplicating your entire database (beyond
simply its tables and views), you can resort to the Copy Database Wizard
(accessible from the Copy Database… entry in the Tasks submenu of the
context-sensitive menu of any non-system database visible in the SQL Server
Management Studio).
Following the initial Welcome page of the wizard, you will be prompted to
select the source and destination servers, along with the authentication method
for each (note that it is possible to use the same SQL Server instance for
both). In general, two methods can be employed to complete this task. The first
one relies on applying detach and attach procedures (which is faster but forces
placing the database in the offline state, making it inaccessible for the
duration of the procedure) – and the second one, using SQL Management Objects,
which is slower, but does not impact database accessibility. (Note that this is
equivalent to the SSIS Transfer Database Control Flow task, which we
presented recently). On the Select Database page, mark the checkbox next to
the source database in the column that determines whether you want to move or
copy it. On the Configure Destination Database page, you will be prompted for
the name of the target database, its files, their size, and location, as well
as the type of action that should take place if a database with the same name
already exists (overwriting it or stopping transfer). Package location, its name,
and logging options (Windows Event log or a text file) are set on the Configure
the Package page. Finally, you are given an opportunity to launch the package
or schedule it for later execution, as well as specify the account in which
security context the package will run). In either case, the package is executed
as a SQL Server Agent job – hence to complete the process successfully, the
corresponding service has to be running.
If neither of these two wizards gives you sufficient flexibility, you can
try scripting creation of required database objects (which is easily
accomplished using the relevant script option in the context sensitive menu of
the appropriate object in the Object Explorer window of SQL Server Management
Studio). Once required objects are created, you can then use Database Import
and Export Wizard to copy data to them.
