THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

SSIS: how to migrate Data Transformation Task of a DTS

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.

Published Monday, October 31, 2005 5:04 AM by Marco Russo (SQLBI)


No Comments
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement