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.