Integration Services Packaging Tools

The last section of this chapter will introduce you to some the other package management utilities that are provided with SQL Server 2005. A Package Migration Wizard is provided to assist you in converting your existing Integration Services packages into SQL Server 2005’s new package format. There’s also a Package Management utility that enables you to work with existing Integration Services packages. In addition, a new command-line tool enables you to run packages from the command line.

Package Migration Wizard

Considering the complete architectural change and the adoption of the .NET Framework, it should come as no surprise that DTS packages built for SQL Server 7 and SQL Server 2000 must be migrated to the new format before they can be modified.

Note 

Existing DTS Packages built for SQL Server 2000 can be run against SQL Server 2005. They can also be included in new SQL Server 2005 Integration Services package solutions. They just can’t be modified unless they are first migrated to the new format.

SQL Server 2005’s Package Migration Wizard will migrate existing Integration Services packages into the SQL Server 2005 Integration Services package format. The Package Migration Wizard attempts to take existing Integration Services package elements like tasks, precedence, constraints, and variables and convert them into the equivalent SQL Server 2005 package items. The Package Migration Wizard can handle the Microsoft-supplied DTS package components, but it cannot convert custom tasks. Custom tasks maintain their old structure and are encapsulated as a subpackage that is linked to the migrated package. Scripts can be another problem issue for the Package Migration Wizard. While most ActiveX transformation scripts can be converted to the new ActiveX Script task, existing scripts that reference the old DTS object model cannot be converted.

Integration Services Package Management Utility

Another command-line tool that’s provided with SQL Server 2005’s Integration Services is the Package Management Utility. You can run the Package Management Utility by entering dtutil on the command line. The dtutil tool enables you to access packages that are stored in SQL Server’s msdb database. It can also be used to copy, delete, and sign existing packages.

Integration Services Package Execution Utilities

Two additional utilities can be used to execute packages: dtexec and dtexecui. As you might surmise from their names, the dtexec tool is run from the command line, while the dtexecui tool displays a graphical user interface that enables you to load and run Integration Services packages. One notable aspect of the dtexec tool is the fact that for enhanced security it can be run using encrypted arguments. The dtexecui tool can be used to generate a text file that contains the commands and parameters required to run the dtexec command line tool (including the option to generate encrypted arguments). You can see the dtexecui tool in Figure 9-14.

Figure 9-14: The dtexecui tool