SQL Server 2005 Integration Services - Import and Export Wizard
March 27, 2006
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):
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.