Upgrading DTS packages to SSIS Packages

Question

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 currently run on SQL Server 2000 and we are being pushed to move off of this in favor of SQL server 2005 (We do not have 2008, it isn't an option).

We have approximately 150 DTS packages that will need to be converted. According to the stack overflow post above, we shouldn't use the conversion tool available. We also anticipate that any "backward compatibility" patches that Microsoft may have released for SQL Server 2005 won't be installed on our server.

Can anyone help me understand the learning curve involved in going from DTS to SSIS? For 150 packages, we've come up with a 900 hour estimate. Myself and a more senior developer will be working on this but we've never worked with SSIS. The 900 hours will be divided between us. As with any project, quality is the utmost importance. Is this estimate outrageous or low?

We will have access to other developers who have worked with SSIS, so we won't be completely alone in this. Also, I've learned that DTS package conversion is not an option; it is not supported by our DBA staff.

Some things we do with DTS include the following. This is by no means an exhaustive list:

• Load XML, XLS, MDB, CSV, etc files in to SQL Server
• Export XML, XLS, MDB, CSV, etc files from SQL Server
• Run Workflow-type processing

Answer 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 [email protected]

Raj