As you already know, the DTS migration wizard of SSIS 2005 doesn’t migrate Data Transformation Task in a Data Flow component, but instead it uses to encapsulate each Transformation Task into a legacy embedded DTS2000 package.

Now, you can imagine my surprise when I tried to migrate a DTS this morning and I’ve got a Data Flow task into a SSIS package. But wait, it’s not so beautiful as we could hope!

This is the starting situation: I had a simple DTS generated by Import/Export Wizard of SQL Server 2000. It copies two tables from Northwind (Customers and Categories) into another database. One of those transformations has been migrated to a Data Flow task, and another to an inner DTS package execution. It deserves to investigate!

The task migrated to a data flow has only “direct row transformations”, while the other has a “ActiveX Script Transformation”, even if this transformation is a simple “copy column” script (it’s the default of SQL2000 Import wizard if you choose the script transformation). Probably I changed the default transformation on one of those task at the time I generated the task (it was a demo I realized while teaching a DTS course some months ago).

My test at this point has been to change the script transformation into a direct one: both Transformation Task has been correctly migrated to a data flow task (one for each original task)!

At this point I’m really excited: why on the earth this wizard still doesn’t migrate some more complex package full of simple Transformation Task which simple map columns directly between source (got with a SQL query) and destination? To make the story short, here are my results (works means successfully data flow migraton, doesn’t work mean a DTS inner package migration):

  • Use of Exception file: partially works (package is migrated successfully, but you lose log settings – BTW I can live with this due to new log provider architecture)
  • Use of Table Lock: works
  • Use of Batch Size (in SQL Server fast load mode): works
  • Use of Max Errors Count: doesn’t work (I have to reset my Max Errors Count to 0 and to handle errors modifying “Error Output” behavior of data flow)
  • Use of UDL file connections: doesn’t work (I can write a script that consolidates my connections in a DTS package removing UDL reference in DTS object model)

Until today I never found a similar schema of what are Data Transformation Task supported features to allow a Data Flow migration instead than a legacy Inner Package execution. I’d like to have an official one by MS.