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.
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:
The tasks that can migrate without any issues include the following:
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:
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:
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