January 21, 2008 at 8:32 am
Hi Guys,
We are upgrading SQL Server 2000 to 2005 and I have to migrate the DTS to SSIS. SQL2K5 no longer has DTS and has SSIS replacing it.
I'm read up on this topic and it seems that Server Management Studio will migrate it for you with its Package Migration Wizard. I was wondering, am I going about this the right way? Are there anything else I should consider? Or should I be doing this another way??
Thanks guys!
January 21, 2008 at 9:24 am
It can migrate some pckages, but depending on if you have scripting and complicated packages, it might not work and you have to re-write.
Pragmatic Works (www.pragmaticworks.com) has a tool that has had more success than the standard migration. The owner is a friend, to be clear.
There is a runtime in SQL Server 2005 to host your DTS packages for now. However that will not be in 2008, so you'll want to learn SSIS over time if you will continue to work with it.
January 21, 2008 at 9:29 am
If you want to learn SSIS, I own part of a training company in Orlando, FL that has gotten great feedback about our SSIS training. Brian Knight, MVP, author, has written the class and usually teaches it.
http://www.endtoendtraining.com
There are other good resources, though I'd avoid the MS curriculum and find a private trainer. also pick up one of the books (any will do) and go through it, practicing the examples.
January 21, 2008 at 12:55 pm
Thanks guys!
I just have 1 more question.
The boss said we can rewrite the DTS in SSIS or run DTS under SQL2000 DTS runtime
What does he mean to run DTS under SQL2000 DTS runtime? Could anyone explain this?
Thanks a lot
January 21, 2008 at 1:12 pm
SQL 2005 has the ability to run SQL 2000 DTS packages. You can even download and install a tool to allow you to edit the DTS packages within SQL 2005 (I think SSMS to be precise). So basically you can keep your 2000 DTS packages intact and run them on your 2005 machine. However, I've found 2 caveats:
1. Like someone else just said, SQL Server 2008 won't support DTS.
2. I've found many instances where I couldn't actually schedule a DTS job in SQL Server Agent. I had to re-write it or convert it to SSIS.
BTW the conversion wizard isn't very good, IMHO.
Aaron
January 21, 2008 at 1:15 pm
You can create an SSIS (SQL Server 2005) package that runs an "Execute DTS 2000 Package Task" around an existing DTS (Sql Server 2000) package.
**Note**: The SSIS package is simply calling the DTS package and there are *NO* performance gains with this method, and you may experience longer execution times. You will see performance gains by rebuilding the functionality of the DTS package from scratch within SSIS.
For more information see SQL Server 2005 Books Online via Technet:
Execute DTS 2000 Package Task
http://technet.microsoft.com/en-us/library/ms137907.aspx
Happs SSISing or DTSing
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
January 22, 2008 at 2:43 am
Like someone else just said, SQL Server 2008 won't support DTS.
SQL Server 2008 will support the same DTS Designer component as SQL Server 2005. There are some blog postings from the SQL authors that confirm this.
They imply that DTS Designer may be supported for future releases. It seems some major cistomers have said they are not prepared to spend $millions just to migrate DTS code to SSIS. afaik DTS will be running quite happily for the next 20 years (hosted in virtual guest servers, as no 32-bit hardware will exist by then).
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
January 22, 2008 at 6:29 am
Take if from someone who's been engaged in doing exactly what you are talking about...re-write your packages in SSIS if you can. You'll get a lot of bang for your buck because there are a lot of new features in SSIS you'll be able to capitalize on, your packages will run faster and they will be far easier to maintain in the future. If you are pressed for time, you can certainly run them in the DTS runtime within SQL2K5 but you also run the risk of management saying, "why change it now, it's working?" and you'll never get them rewritten. If you can, spend some time rethinking how your packages work and rearchitect them for SSIS. It'll pay off in the long run.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply