SSIS and DTS

This past Saturday I delivered a one-day seminar on SQL Server Integration Services for the local chapter of the ACM. Based on the setup information I'd received leading up to the seminar I'd prepared an introductory (this is Control Flow, this is Data Flow...) set of slides, but I was very pleasantly surprised Saturday morning. There were around 20 people attending, and almost every one of them had some previous SSIS experience, so we covered much more information and much more interesting topics than I'd planned. I had a great time, and based on the feedback I've already received post-session from the attendees, they did as well.

One of the topics we touched on was that of migrating from DTS to SSIS. Anyone who has spoken to me on this topic before knows my basic stance, but it's worth summarizing here:

• DTS was a simple tool for solving simple problems.[1]
• SSIS is a powerful tool for solving just about any data integration problem, but while it makes it easy to do things the right way, it's not always simple to do simple things.[2]
• You can run DTS and SSIS side-by-side on the same SQL Server 2005 server.
• If you have DTS packages that do what you need them to do, leave them as DTS packages. Don't attempt to migrate/upgrade them to SSIS.
• The DTS migration tools that supposedly allow you to upgrade DTS packages to SSIS packages largely exist to make the VB6 -> VB.NET upgrade tools look good.

And it was this last point that prompted this post. One of my students from Saturday asked about running DTS packages with SQL Server 2005. Here's her question:

"On Saturday you mentioned that you recommend importing old DTS packages into SSIS because there isn’t a migration tool.[3] This would allow us to keep using our old packages without having to rewrite them prior to upgrading to SQL Server 2005. My questions is would we be able to open and edit the old DTS packages or just open and run them?"

The short answer is that SQL Server 2005, out of the box, only allows you to run DTS packages, not to edit them. So when SQL Server 2005 was released, you still needed a SQL Server 2000 machine with Enterprise Manager installed in order to make changes to your DTS packages or (lord help you) to build new ones. Thankfully, Microsoft has released a "Feature Pack" for SQL Server 2005 that includes the Microsoft SQL Server 2000 DTS Designer Components, which allow you to edit DTS packages in SSMS or BIDS without needing Enterprise Manager installed.

You can download the Feature Pack components here: http://www.microsoft.com/downloads/details.aspx?FamilyID=50b97994-8453-4998-8226-fa42ec403d17&displaylang=en

There is also a good overview topic on SSIS backward compatibility in SQL Server Books online, which you can find online here: http://msdn2.microsoft.com/en-us/library/ms143706.aspx

Enjoy!

[1] Canned peaches, remember?
[2] That's right - fresh peaches!
[3] Please note that I didn't exactly say that there was no migration tool. I simply said that there was no migration tool that worked for non-trivial real world packages. ;-)