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. 😉

Advertisements

About ssimagine

My name is Matthew Roche, and I am a Senior Program Manager with the SQL Server product group at Microsoft. I work on Master Data Services and Data Quality Services, and have previously worked on SQL Server Integration Services. Although I work for Microsoft and will be posting on technical topics, I want to stress that this is a personal blog, and any opinions posted here are mine and mine alone. I built my career around SQL Server and Microsoft technologies for well over a decade before I joined Microsoft as an employee, and I plan on using this blog to share my personal experience and opinions. They may well be shaped by my experience on the SQL Server team, but they’re still mine, and not that of Microsoft, disclaimer, disclaimer, etc., etc..
This entry was posted in BI, SQL Server, SSIS. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s