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 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).

  • Why is my T-SQL cursor executing twice?
  • DB column extract and split to find other table result
  • How can I change my default database in SQL Server without using MS SQL Server Management Studio?
  • sql script execution fails when called using ANT SQL task
  • Delete items older than a day - SQL Server
  • Improve query to join table with select statement in t-sql
  • 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

  • SQL Server 2000 DTS - Cannot resolve collation conflict for equal to operation
  • How do I start the dts transfer program?
  • Performance of update statement inside DTS package
  • Execute SQL Server 7 DTS on SQL Server 2008
  • Find a reference to a table in DTS packages
  • SSIS XMLSource only seeing null values in XML variable
  • One Solution collect form web for “Upgrading DTS packages to SSIS Packages”

    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


    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.