Post reply

DTS Packages migration

  • August 12, 2011 at 11:06 am

    #241619

    Hi,

    I need transfer the DTS packages from sql server 2000 to New sql server 2000.

    totally (89 dts packages) in server.

    How to move ?

    Any script is there or through dts ?

    Please help me

    Thanks

    jerry

  • August 12, 2011 at 11:15 am

    #1368112

    There is nothing built-in to do this. However this wonderful (sorry for the self agrandizing) tool can help:

    http://ncldts.codeplex.com/[/url]

    If you need assistance or have questions let me know.

    CEWII

  • August 12, 2011 at 11:16 am

    #1368113

    Are they all stored in MSDB or the file system?

    Also, I am curious why stay with SQL 2000?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • August 12, 2011 at 11:24 am

    #1368121

    Besides Elliotts slick tool, you can do this easily from msdb database to msdb database via sql script and a dts transfer.

    Here would be the first part of that method.

    SELECT T1.* FROM dbo.sysdtspackages AS T1

    INNER JOIN (SELECT [name], [id], MAX([createdate]) AS [createdate]

    FROM dbo.sysdtspackages GROUP BY [name], [id]) AS T2

    ON T1.[id] = T2.[id] AND T1.[createdate] = T2.[createdate]

    And here would be the final insert sample:

    Insert into sysdtspackages (name,id,versionid,description,categoryid,createdate,owner,packagedata,owner_sid,packagetype)

    Select name,id,versionid,description,categoryid,createdate,owner,packagedata,owner_sid,packagetype

    From DTSPackageTransfer

    Where Description <> ''

    And now, off to write a blog about it

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • August 12, 2011 at 12:13 pm

    #1368144

    I never used the EW's slick tool but I have used DTS Backup.

    http://www.sqldts.com/242.aspx

    Why are you creating a new SQL Server 2000 machine?

    As was suggested you might want to consider upgrading.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply