Microsoft SQL Server 2005: Changing the Paradigm (SQL Server 2005 Public Beta Edition)

As in previous releases of SQL Server, if you do not want to use the package designer, you can simply step through the Import/Export Wizard to create an SSIS package. The following sections discuss the Import/Export Wizard, the SSIS Configuration Wizard, the Package Installer Wizard, and the SSIS Migration Wizard.

The SSIS Import/Export Wizard

The SSIS Import/Export Wizard guides you through the steps to copy data between different data sources. It guides you to connect to the source and destination and allows different properties, such as copying constraints and copying data results from SQL query. You can access this wizard from BIDS, and the command prompt. To launch the wizard from BIDS, you open the Solution Explorer window, right-click the SSIS Packages folder, and select the Import and Export Wizard menu item. To launch the Import/Export Wizard from the command prompt, you run DTSWizard.exe.

This wizard allows you to select the source and destination data sources. The data sources supported are Microsoft SQL Server, flat files, Microsoft Office Access, Microsoft Office Excel, and other OLE DB providers. You are prompted to copy all the data or a portion of the data, based on a user-defined query from one or more tables or view. The wizard gives you the option to either execute the package immediately or save the package. The package can be saved either in the msdb database in SQL Server storage or in the XML format, as .dtsx file storage.

The SSIS Configuration Wizard

Configurations are loaded at the start of process runtime to dynamically set the values of variables and object properties. This feature is useful if the same package has to be deployed on different servers and you want to assign the properties automatically when the package starts running. The SSIS Configuration Wizard guides you in configuring the configurations to be deployed. To launch the SSIS Configuration Wizard from BIDS, you open the package in the designer and then select SSIS | Package Configurations to launch the Package Configurations Organizer. Then you check the Enable Package Configurations check box and click Add to start the Configuration Wizard.

On the first page of the wizard, you are prompted to select the configuration type. The different types of configurations available are the XML configuration type, environmental variables, registry entries, parent package variables, INI files, and SQL Server.

On the Select Objects to Export page, you are prompted to include the package objects to set the properties. Based on the configuration properties, the configuration file is created. You can edit the file to edit the properties set, if required.

The SSIS Migration Wizard

The SSIS Migration Wizard allows migration of existing DTS packages in SQL Server 2000 to SSIS in SQL Server 2005. In SQL Server 2000, the packages could be stored in structured storage, SQL Server databases, and the metadata services. Though the format of the package storage has changed in SSIS (files are now stored in XML format on the disk drive), the wizard allows you to migrate and store these packages to the disk by using the .dtsx XML format or to SQL Server databases. Not all DTS 2000 packages can be migrated to SSIS because some of the tasks are not available in SSIS or have been changed significantly. Even the DTS object model has changed, so packages created using the DTS object model cannot be migrated. Before looking at Migration Wizard details, let's discuss what can and cannot be migrated to SSIS.

Migrating DTS 2000 Packages to SSIS

SSIS provides the option to migrate your existing DTS 2000 packages to SSIS either manually (by re-creating the packages from scratch) or by using the SSIS Migration Wizard. Due to the changes in the SSIS architecture, not all the tasks can be migrated to SSIS. Also, some of the tasks have been removed from SSIS, and some of the tasks have changed, making migration a possibility in only some cases. You can also choose to leave a package as it is in DTS 2000, and can use the Execute DTS 2000 Package task to execute it in SSIS.

Note

The DTS 2000 runtime engine must be installed on the computer to run DTS packages by using the Execute DTS 2000 Package task.

The following tasks from DTS 2000 cannot be mapped to SQL Server 2005 tasks:

  • Custom tasksbecause the DTS object model and architecture have changed in SSIS

  • Data Pump tasks such as the Data Driven Query task, the Transform Data task, and the Parallel Data Pump task

  • SQL Server tasks such as Copy SQL Server Object, Transfer Databases, Transfer Jobs, Transfer Master Stored Procedure, and Transfer Logins

  • Analysis Services tasks such as Analysis Services Processing and Data Mining Prediction

  • Other tasks, such as Dynamic Properties and Transfer Error Messages

  • Copy Database Wizard tasks

The tasks that can migrate without any issues include the following:

  • The Execute SQL task

  • The Bulk Insert task

  • The File Transfer Protocol task

  • The Execute Process task

  • The Send Mail task

  • The Message Queue task

Although there is an equivalent ActiveX script task in SSIS, an ActiveX script that contains code based on the old DTS 2000 object model cannot migrate because the new SSIS object model has changed. In addition, if the script contains or refers to global variables, the task cannot be migrated successfully. The tasks that cannot be mapped to SSIS are encapsulated into a SQL Server package task by the SSIS Migration Wizard and executed by the Execute DTS 2000 Package task.

Although package passwords are not migrated with the packages to the SSIS format, the SQL Server Package task maintains its password. The Execute DTS 2000 Package task contains the property to specify the passwords.

In DTS 2000, there was no direct method to implement looping; the user could hack into the DTS object model to work around this. If you have implemented DTS 2000 packages with looping, it is better if you re-design those packages to make use of the new looping container in SSIS.

Elements such as precedence constraints are migrated easily to their equivalent SSIS elements. After migration, a precedence constraint can be extended to include or overwrite the result of the precedence with conditions under which the task can run or to combine multiple precedence constraints on a single condition.

In case of connections, the connections associated with tasks that can be migrated are also migrated to the equivalent OLE DB or ODBC connection manager, and properties that are not set in the connection manager are set to their defaults. For tasks that cannot be migrated, such as the Transform Data task, the connections remain part of the intermediate SQL Server package.

DTS 2000 does not define scope for variables, but it considers all the variables as global variables with the package as the scope. All the global variables can be migrated to SSIS and are added to the variables collection of the package in the User namespace. You can extend the functionality of these variables by creating additional namespaces, using expressions to set variable values, or raising events based on a variable's value.

Migration of transactions to SSIS is supported, but because of the difference in how the transactions are defined in DTS 2000 and SSIS, the successful migration of transactions depends on certain conditions. In DTS 2000, the transaction is tied to steps, which are the combination of the task and its associated precedence constraint. There was no concept of containers in DTS 2000. In SQL Server 2005, the transactions in the package scope follow the container hierarchy. These are the rules that govern transaction migration:

  • If the step in the DTS 2000 package is configured to join a transaction, the transaction attribute on the migrated task is configured to support a transaction.

  • If the DTS 2000 package is configured to use transactions, the transaction attribute on the migrated package is configured to require a transaction.

  • If the DTS 2000 package does not use transactions, then the transaction attribute on the migrated task is configured to support a transaction.

Considering the architectural and performance enhancements in SSIS, the introduction of new tasks and containers, and the fact that not all DTS 2000 tasks can be successfully migrated to SSIS, it is recommended that you consider re-designing old packages from SQL Server 2000 to the SSIS model instead of migrating the old packages.

If you decide to migrate old packages to SSIS instead of re-designing them, the easiest way to do so is to use the SSIS Migration Wizard, as discussed in the next section.

Using the SSIS Migration Wizard

To start the SSIS Migration Wizard, you right-click the SSIS Packages folder in the Solution Explorer in BIDS and select Migrate DTS 2000 Package. Alternatively, you can run dtsmigrationwizard.exe.

The wizard guides you through the following steps in order to migrate the package:

1.

Select package sourceThe wizard allows migration of packages stored in structured storage files, SQL Server databases, and the SQL Server metadata services. Packages saved in Visual Basic cannot be migrated. For packages saved in structured storage files, you can migrate some or all of the packages and versions saved in the file.

2.

Select package destinationThe packages can be migrated either to XML format as .dtsx files or to SQL Server 2005. You need to provide proper authentication and should have appropriate priveleges in order for the wizard to save the package to the choosen storage destination.

3.

Select packages to migrateThe wizard prompts you to select the packages to migrate to SSIS. You can either choose to change the package names in the wizard or to do that after the migration, in BIDS or SQL Server Management Studio. During the package migration, you are prompted to supply the password if the packages are password protected.

4.

Specify Log fileThe wizard prompts you to specify a log file location. After running the SSIS Migration Wizard, you should always review this log file so that you can understand any warnings or errors that are encountered.

Based on the parameters you provide in the wizard, the SSIS Migration Wizard tries to migrate the packages to SSIS. The wizard displays the migration progress for each of the packages selected for migration. If the wizard is unable to migrate a package, it prompts you to either continue migrating the other packages or end the migration process.

As discussed earlier, not all the tasks in DTS 2000 packages can be migrated. For tasks that cannot be migrated to SSIS, such as Data Pump tasks, custom tasks, Transfer Database Object tasks, and so on, a SQL Server 2000 package is created to maintain the SQL Server 2000 format. The SQL Server 2000 package is then executed by the Execute DTS 2000 Package task. But a package that contains such tasks cannot be migrated to SSIS as a single package. A SQL Server 2000 package is created for each task, and each of these tasks executed by the Execute DTS 2000 Package task is encapsulated within the main package. For example, if there are two Data Pump tasks in a package, each task is converted into equivalent SQL Server 2000 package, resulting in three packages. Inside the main package, these two packages are called by the Execute DTS 2000 Package task to execute these packages.

The SSIS Migration Wizard issues a warning if the package choosen for migration contains the Dynamic Properties task. If you choose to migrate the package, the task is replaced by a Script task, which lists the propertyvalue pair defined in the Dynamic Properties task, and this script is commented out.T

Related

Категории