November 7, 2013 at 6:01 am
Hi,
we have migration work which we need to migrate our sql 2000 server ,sql 2005 server to sql server 2008 r2.
In that we have DTS packages in sql 2000 and sql 2005 servers.
is difference in between sql 2000 and sql 2005 server to migrate DTS.
I need to migrate dts packages from 2000 to sql 2008 r2?
I need to migrate dts packages from 2005 to sql 2008 r2?
Any one please let me know the steps and plan how to do that.
Advance thanks...
November 7, 2013 at 6:08 am
I find it much easier, and far less time consuming just to take the logic and write new SSIS packages. There, you always find better ways of writing the code, and migrating the data. It may sound daunting now, but in the long run, you will save time, effort and frustration. Plus, what you used in DTS, there are much more efficient methods in SSIS and C#
Andrew SQLDBA
November 7, 2013 at 6:48 am
It is possible to migrate DTS packages to SQL2008R2, but I would also agree with Andrew. Long-term, you and your users will be better off migrating the DTS packages to SSIS.
However, if you're looking at a hard deadline with insufficient time to give the devs to create SSIS packages to do what is needed, here's the two links I used when I had to do this:
Install support for DTS packages in SQL2008R2
How to copy DTS packages to SQL2008R2
You'll notice in the second article, the author makes the same recommendation, migrate to SSIS instead...
November 7, 2013 at 6:58 am
In 2000 server we have list of all packages deployed in the MSDB database and we dont any Filesystem packages.
Can we do in below way for migrating the those msdb packages.
1)restore the 2000 MSDB database on sql server 2008 r2 with other name and moved those packages to (select * from msdb.dbo.sysdtspackages) to msdb sql server 2008 r2.
2)script the assocatied jobs for that dts packages and run on destination server(sql server 2008 r2)
Kindly confirm is it correct way to follow?
November 7, 2013 at 7:15 am
Not possible. You cannot restore an earlier version of any database, to a more recent. Besides, SQL 2005 and above does not know how to deal with DTS. Those versions know SSIS. the Integrated Services would not know how to deal with them either. DTS and SSIS are two, totally different things.
It does not matter where the SSIS packages are stored. I never deploy mine to a drive, I always keep them in the MSDB database. That way they are always safe in the backup.
Andrew SQLDBA
November 7, 2013 at 7:42 am
bala2 (11/7/2013)
Can we do in below way for migrating the those msdb packages.
1)restore the 2000 MSDB database on sql server 2008 r2 with other name and moved those packages to (select * from msdb.dbo.sysdtspackages) to msdb sql server 2008 r2.
quote]
Presuming you're talking about following method 2A in the second article I linked (restore old MSDB to a new DB such as MSDBOLD,) then yes, that will work.
That's the method I used to migrate DTS packages from a SQL2005 to SQL2008R2. All the time, of course, reminding the dev / users that they need to start migrating their packages to SSIS, as there is *NO* DTS support *AT ALL* in SQL2012, which at some point we will need to migrate to. Better and less stressful for all concerned if they do this now, rather than when the deadline is closer and moving fast...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply