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

  • SQL Server 2012 Management Studio for Windows XP
  • How to get row with last day of month in Sql Server query
  • How can I check for average concurrent events in a SQL table based on the date, time and duration of the events?
  • how to connect to SQL Server with SQuirreL SQL from non domain registered Linux box?
  • show create table tablename (how do i do this in sql server)?
  • In SQL Server, how do I generate a CREATE TABLE statement for a given table?
  • 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

  • SSIS XMLSource only seeing null values in XML variable
  • How can I run sql server stored procedures in parallel?
  • Execute SQL Server 7 DTS on SQL Server 2008
  • Get DTS Step Description from TSQL?
  • How do I start the dts transfer program?
  • SQL Server: importing from Excel, only want the new entries
  • 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

    Raj

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