Migrating SS2000 DTS to SS2005 SSIS RRS feed

  • Question




      Is it possbile to migrate SS2000 DTS packages to SS2005 SSIS involving databases with compatibility mode set to 80. If yes, what are the potential issues, which may occur?





    Tuesday, March 11, 2008 4:29 PM

All replies

  • There is a wizard to do this!

    Issues are many a varied

    DTS and SSIS are different in concept.



    You can run the DTS packages as DTS packages within a SQL2005 enviroment.

    If you have 2005 you can try the migration tool.


    for more help

    how many DTS packages do you have?

    how complex do they get?



    Tuesday, March 11, 2008 8:16 PM
  • We have quite a few DTS package(a couple of hundred)..complexity ranging from very simple to very complex.


     We want to continue use DTS packages in SS2005 rather then migrating to SSIS, as of now and we have a proper documentation on how to achive this.


     My concern is, are there any documented issues, with having such a strategy?




    Wednesday, March 12, 2008 9:10 AM
  • If you do not want to run DTS packages without migrating

    them through the tool "Package Migration Wizard" SQL Server Provided, you can use "Execute DTS 2000 Package" Task to run DTS packages.


    To use this task, firstly make sure you set up the Execute DTS 2000 Package" Task by selecting legacy components when installing SQL Server 2005.


    One thing you might pay attention to is you can not run DTS package stored in Repository or Meta Data Services directly in SQL Server 2005 IS. If you need to run those packages in SQL Server 2005, you can embedded them in a IS packages by selecting a option "Embeded in Task" in "Execute DTS 2000 Package" task, when set, the DTS package is embedded inside the IS package and the IS packages can run in IS 2005. One drawback here is that, depending on the size and number of embedded packages, your parent package (the IS package containing DTS package) disk size can grow substantially larger.



    Wednesday, March 12, 2008 10:26 AM
  • Hi,


    Instructions on installing DTS 2000 designer http://msdn2.microsoft.com/en-us/library/aa544774.aspx


    I hope this helps,



    Wednesday, March 12, 2008 6:01 PM
  • We can continue running our old DTS packages using dtsrun as we did earlier. This is because the DTS runtime environment continues to work.


    To run a SQL Server 2000 DTS package from a SQL Server Agent job, we cannot use a job step of type, SQL Server Integration Services Package. Instead, we use a job step of type, Operating system (CmdExec), and run the dtsrun.exe utility as the command.

     We are following the above strategy.




    Tuesday, March 18, 2008 12:40 PM
  • If you have questions on the migration process or need more resource pointers, feel free to ask.


    Monday, March 24, 2008 5:37 AM