Getting into SSIS

For the last 3 days I've been doing a course on SQL Server 2008 Integration Services (6235A if you want all the details). I've been working with SQL Server across 2000, 2005 and recently 2008, and I'm fairly confident that I can keep the servers up and running, but the majority of work I've actually done with databases has been on SQL Server 2000. That being the case, I've done a lot of work with DTS, but haven't really got my feet wet with SQL Server Integration Services (SSIS), which replaced DTS from SQL Server 2005.

Since mainstream support for SQL Server 2000 has already lapsed, having a stack of production databases running on it is not best practice, so we've been in the process of moving on for a while. We were fortunate enough to have some great advice from Microsoft's Andrew Fryer who helped with the design of our server architecture and went through various migration tools including the SQL Server 2008 Upgrade Advisor which found surprisingly few issues. The biggest thing it flagged up was the abundance of DTS packages which should be updated to SSIS. That said, you may be able to get away with running the packages via the Data Transformation Services 2000 runtime which is part of the Microsoft SQL Server 2005 Backward Compatibility Components in the Microsoft SQL Server 2008 Feature Pack (at the time of writing the latest version is from April 2009, but you may want to search in case there's a newer one).

Normally learning a technology like SSIS is something that I'd do by just getting on and playing around with it, but this is a clear case where that probably wouldn't have worked out very well. There's a lot to SSIS, and a lot of it that's not like anything else, so you're unlikely to be able to discover it no matter how savvy you are. That point was proven by a colleague who came on the course having used SSIS for some time - it wasn't until day 3 that he really learned anything that he hadn't worked out in the past year of working with SSIS, but the things he did pick up made it worth attending.

Of course the fact that you can manage SQL 2008 with PowerShell is a huge plus for me, and I intent to dive into that next, but after this course what I really want is this PowerShell Script Task for SSIS (currently in development on CodePlex).

Some other resources that were mentioned in relation to SSIS, other SQL BI or just SQL in general that may be worth checking out if you're working with this stuff:

• Microsoft SQL Server Development Customer Advisory Team blog
• BI Polar
• SSIS Junkie
• SSIS Team Blog
• SSISBI.com
• MSDN SSIS Forum