So there I was sitting the Upgrading to SQL 2005 one-day course last week and along with all the why upgrade and considerations when you upgrade questions, there was one question that I realised that EVERY single person in the group wanted to know about:
Exactly what is the deal with DTS packages when you upgrade to SQL Server 2005? Since Jamie is always on about SSIS and all the magic it can do, I thought I'd add a penny or two to the plate taken from the discussions we had in class regarding upgrades:
When you upgrade your system to SQL 2005 your DTS packages REMAIN DTS packages. They are NOT converted at this point. They will continue to execute in what is known as legacy compatibility mode. The catch here is that once you got them into SQL 2005 you cannot change or edit them. The word on the street is that your DTS packages are almost guaranteed to work when run in this legacy compatibility mode.
In order to change your DTS packages you will need to convert them to SSIS packages. There is a conversion utility that will do this for you. The catch here is that the conversion is performed as “Best Endeavours” and Microsoft in no way guarantees the upgrade of your DTS packages.
It became clear to everyone in class that day was that the lesson to learn here is that when you start planning your SQL 2005 upgrade, start looking into redesigning your DTS packages at the same, especially if you have custom packages or very many of them!
As a side note: The course was presented by Ishfaq Ahmed, a Microsoft Dedicated Support Engineer. These group of engineers are "dedicated to proactively supporting Microsoft's enterprise customers" and now they've been drafted to present various "Notes from the Field" courses to (mostly) Microsoft Partners.
- Marlon