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 – the full source code is available.

Introduction

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 only made up of embedded DTS packages – each one containing a single task – is not what you want. Performance and maintainability become a nightmare; for that reason I prefer to leave a DTS package in its native form until I need to migrate and – probably – to refactor the package, possibly changing parts of its behavior in the process.

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 presents many limitations when it comes to migrating a Transform Data Task into a native SSIS Data Flow Task. DtsToSsis-Prepare does a few modifications to the original DTS package, resulting in another DTS package that has a higher chance of being completely migrated.

Package Migration Wizard limitations

I tried to identify what settings of a Data Flow Task might block the migration, leaving the task in an embedded legacy package. Please note that “not supported” literally means that the setting is not supported by the Package Migration Wizard; thus if it is present, the migration will result in an embedded DTS package containing only one task – the worst possible case to maintain.

  • Exception file: lost in migration, but its functionality can be restored by adding error flow handling into the Data Flow Task of an SSIS package.
  • Table lock: supported in migration
  • Batch size: supported in migration
  • Max errors count: not supported if different than zero.
  • UDL connections: not supported if present (as a connection)
  • Advanced transformations: ActiveX Script transformations are not supported.

 

To work around any of the limitations described, 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 transformations.

 

The goal for DtsToSsis-Prepare is to automate those operations. Unfortunately, in case there are any “Advanced Transformation” they should be manually converted into a corresponding Transform Data Task; indeed, the user needs to create a design – into the resulting Data Flow – for their 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 only use 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 only serve one data task at a time. The picture below illustrates the 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 into 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 well 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:

DtsToSsisPrepare [switch:value]

The mandatory switches are input and output files, but you probably want to add conversion options. The one we suggest is to enable all features, for example:

DtsToSsisPrepare /DtsSource:original.dts /DtsDestination:prepared.dts /all

The following list contains all the options available.

  • /ConsolidateUdl
    Consolidate UDL connections into regular connections.
  • /OptimizeConnections
    Optimize connections recycling the same connection for several tasks.
  • /RemoveAdvancedTransformations
    Remove advanced transformations phases from Transform Data Task.
  • /ResetMaxErrorCount
    Reset Maximum Error Count property from Transform Data Task.
  • /DtsSource
    Source DTS file name.
  • /DtsDestination
    Destination DTS file name.
  • /help
    Show help.
  • /all
    Activate all conversion features.