Having cut my teeth on SQL Server 2000, I had some (but not a lot) of DTS experience.  Recently I found myself presented with a massive ETL project, and found a perfect opportunity to get started with SQL Server Integration Services.

With the help of a couple of great books and one magazine (I'll mention those shortly), I have spent the past two weeks neck-deep in SSIS.  Armed only with my limited DTS experience and the memory of a few SSIS sessions from last year's DevConnections conference, I started my project slowly and built momentum quickly.  My first surprise was that SSIS allows two-way FTP communication (DTS only allowed download unless you created a custom routine for upload), which saved me at least a couple of hours of programming.  The horror stories of ActiveX scripting in DTS are told far and wide; SSIS removes this time drain by allowing script objects to bring the full power of the .NET framework into scripting tasks.  From an aesthetic standpoint, the interface in SSIS is much cleaner, and the container objects allow for logical grouping of tasks.  After using SSIS, can I ever again use DTS without whining?

Now I don't claim that a few weeks at the wheel of SSIS has made me an expert by any means; a quick trip through the forums at SQLServerCentral remind me that I've still got lots to learn about this tool.  However, in that short time I found enough to get my feet wet with SSIS, and I know find myself reviewing existing processes to see if any of them could be improved by using SSIS.

For those who have little or no experience with SSIS, there are a couple of resources I highly recommend.  Two books, Pro SQL Server 2005 by APress, and Professional SQL Server 2005 Integration Services by Wrox proved very useful in my foray into SSIS.  The SSIS chapter in the former is very useful (as is the rest of the book), and the latter includes expanded and in-depth coverage of this topic.  Finally, the November/December 2006 issue of SQL Server Standard magazine was almost entirely dedicated to SSIS, and is clear and well written.