DtsToSsis-Prepare is a command line tool that prepares a DTS package for a better migration to an SSIS package.
This article describes the needs for this tool and how to use it.
The project is freeware, full source code is available.
The migration of a DTS package to a SSIS package can be accomplished by the Package Migration Wizard included in SQL Server Management Studio. The wizard tries to generate an SSIS package that is functionally equivalent to the original DTS package, but when the conversion of a task is not safe, that task is persisted into the resulting SSIS package as an embedded DTS package containing only the task that can’t be migrated.
Having an SSIS package composed by only embedded DTS packages (each one containing a single task) is not a desired result. Performance and maintainability become a nightmare and, for that reason, I prefer to leave a DTS package in his native form until I need to migrate and (probably) to refactor the package (may be changing parts of his behavior).
When you migrate a package originally designed to move data into a Data Warehouse, chances are that you have a lot of Transform Data Tasks combined with some Execute SQL Tasks. Migration Wizard easily migrates Execute SQL Tasks into native SSIS Execute SQL Tasks, but it has many limits to migrate a Transform Data Task into a native SSIS Data Flow Task. DtsToSsis-Prepare makes some modifications to the original DTS package resulting in another DTS package that has more chances to be completely migrated.
Package Migration Wizard limitations
I tried to identify what settings of a Data Flow Task may block the migration, leaving the task into an embedded legacy package. Please note that “not supported” literally means that the setting is not supported by the Package Migration Wizard and if it is present then the migration will result in an embedded DTS package containing only one task (the worst case to maintain).
- Exception file: it is lost in migration, but its functionality can be restored adding error flow handling into the Data Flow Task of a SSIS package.
- Table lock: it is supported in migration
- Batch size: it is supported in migration
- Max errors count: it is not supported if different than zero.
- UDL connections: it is not supported if present (as a connection)
- Advanced transformations: ActiveX Script transformations are not supported.
To workaround described limitations, you need to make some modifications to the DTS package:
- Max errors count: set it to 0 (zero)
- UDL connections: consolidate UDL connections in “standard” OLE DB connections
- Advanced transformations: remove advanced transformation
The target for DtsToSsis-Prepare is to automate those operations. Unfortunately, we still require a manual modification in case of presence of the “Advanced Transformation” into a Transform Data Task, because the user needs to create a design (into the resulting Data Flow) for his transformation.
How DtsToSsis-Prepare works
The following picture shows a DTS package before migration: each transformation has batch size, max error count and exception file set to non-default values.
This package has 4 UDL connections that use only 2 UDL files: one for the Staging database, the other for the DataMart database. This is necessary in a DTS package to allow parallelism between transformations (the same connection can serve only one data task at a time). Next picture illustrates connections structure of the initial DTS package.
If we try to migrate this DTS package into an SSIS package, we obtain this result.
It is not very useful to get one SSIS package containing two DTS packages.
Using the DtsToSsis-Prepare tool with all options enabled we get this “prepared” DTS package.
As you can see, UDL connections have been converted to standard connections and have been consolidated into only two physical connections. At this point this DTS package would be less parallelized, but we will never execute this package, we just want it to migrate in a fair way to SSIS. The next picture shows the result of this migration.
We have two Data Flow Tasks corresponding to our original Transform Data Tasks.
Each one is a simple transformation, but note that we have a total of only two connections in the SSIS package: we have the original DTS parallelization capabilities but only two connections to configure with the new SSIS capabilities.
DtsToSsis-Prepare syntax and options
DtsToSsis-Prepare is a command line utility with this simple syntax:
The mandatory switches are input and output files, but probably you want to add some conversion options; the suggested one is to enable all features, for example:
DtsToSsisPrepare /DtsSource:original.dts /DtsDestination:prepared.dts /all
The following list contains all the options available.
Consolidate UDL connections into regular connections
Optimize connections recycling the same connection for several tasks
Remove advanced transformations phases from Transform Data Task
Reset Maximum Error Count property from Transform Data Task
Source DTS file name
Destination DTS file name
Activate all conversion features