Upgrading Dts Packages To Ssis Packages

My question is similar to Upgrading SQL Server 2000 to 2005 or 2008 - DTS to SSIS but I have a few restrictions that I'd like the community to consider. We have two databases that

Solution 1:

The built in conversion tool will not

• migrate transaction settings
• migrate ActiveX scripts associated with steps
• Analysis Services task
• complex Data Transformation tasks
• custom tasks
• Data Driven Query tasks
• Data Mining Prediction Query task
• Dynamic Properties tasks
• Execute Packages tasks
• Parallel Data Pump tasks
• tasks that obtain their connection information from Microsoft Data Link (.udl) files
• ActiveX Script code that accesses the DTS object model through the Parent property of the GlobalVariables collection

There is a tool called dtsXchange available from Pragmatic works which does most of these conversions, but still it does not re-engineer the package.

Typically, a DTS package would use staging tables and most of the transformation / data manipulation would happen using SQL or Stored procedures. Automatic conversion would continue to use the same work flow.

The SSIS engine is quite powerful and has a lot of features to help do these tasks. Rewriting the packages in SSIS would be the way to go.

Having said that, considering that you do not have any experience with SSIS, I feel that 900 hours for 150 packages is a bit conservative. I think you should plan for 8 hours per package, but aim to do it in 6 hours.

I hope you are not using 64 bit. Neither XLS nor MDB files have a 64 bit data provider and you will be forced to run in 32 bit mode.

I have been working on a strong argument for rewriting against conversion. I will gladly share all the documentation I have with you. Email me at b.rengarajan@gmail.com

Raj