October 27, 2006 at 4:43 pm
Hello,
We are in the process of planning a migration from Sql Server 2000 to Sql Server 2005 (Yukon). We have been concerned with migrating our DTS Packages to Sql Server 2005 via Sql Server 2005 Integration Services (SSIS).
We were wondering if anyone who has experienced this migration could share some of his or her experiences ?
How smooth was the DTS migration process to 2005 overall ?
Did you use the Package migration wizard or some other method ?
Any loss of functionality or performance with transferred packages ?
Any suggestions or special considerations that we should be aware of prior to performing our DTS migration ?
What was your biggest obstacle while performing the DTS migration ?
The Microsoft site makes the DTS migration to Sql Server 2005 seem very easy and straightforward.
Is the DTS migration as smooth as they suggest ?
Thanks in advance for sharing your expertise with us.
Sincerely,
Don
October 30, 2006 at 7:34 am
Don,
We too have a ton of DTS packages on our existing 2000 servers and we are now deploying our 2005 farm. At first, we planned to do a slow transition to 2005 with our packages and we were going to convert them to SSIS packages. During our testing and research on the subject, we found that depending on what the DTS package is doing will determine how well it converts. Simple things like SQL Tasks convert just fine. But things like data transformations and ActiveX Script tasks and Dynamic Property tasks will not. The migration wizard simply wraps the functionality that does not convert into mini-DTS packages. So what you might end up with is a SSIS Package that has SSIS tasks and tasks that run your legacy DTS tasks.
While this may be okay for you, we felt this was not the way to go for us. It made sense to us to either completely rewrite the packages 100% SSIS or to leave the DTS packages as they are and just move them to the 2005 servers. SQL Server 2005 is capable of running DTS packages (see Mgmt Studio-Management-Legacy-DTS). You can still schedule a DTS package in a job like you did in 2000. So our plan is to move the DTS packages to 2005 as is and then slowing convert them to 100% SSIS. This allows us to not touch something that isn't broken. Of course, we are guessing that DTS will not be supported in the long run, so we will do the conversion.
Other things you will need to consider is how you store the SSIS packages on your server (file or in MSDB), will you be using package configurations, will you be using checkpoints, and how will you stored logging data. We are currently working through these issues ourselves and are running into some problems along the way. We have found very little on how managing SSIS packages in the real world and what some of the best practices are. It seems most people are storing packages and related files as files. To us, this introduces new security risks and additional configuration and management issues.
Good luck with your implementation!
March 13, 2007 at 1:26 pm
Lee,
After you figure out how you will store your SSIS packages (store in SQL Server vs store as external files, if I am correct about the choice), I hope you post your experiences here. This is a question that I have (assuming I can ever figure out how to store them in SQL Server).
- Patrick
March 14, 2007 at 7:39 am
PStair,
We are going the route of storing the SSIS packages on the file system rather in SQL Server. We are slowly getting our 2005 environments up and we just recently have been working through all the security concerns to make this work. As we get further along, I will try to return to this post and let you know how it is coming.
April 17, 2008 at 4:54 am
hey,
I'M in same situation now with a little but importan diffrent. I also like to convert the charcather set from latin1 to unicode.
Is somebody in same situation. I don't find any solution at the moment, which is fast enought.
I start bei SQL-Server 2000 latin1 and will come to SQL-Server 2005 unicode.
Thanks for any help
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply