February 2006 SQL Server Melbourne SIG - Peter Myers

Last night's Melbourne SQL Server Special Interest Group (SIG) meeting was presented by Peter Myers on "Developing, deploying and managing a SQL Server Integration Services implementation" (view event details on sqlserver.org.au or download a pdf of the slides).

SQL Server Integration Services (SSIS) is the successor to Data Transformation Services (DTS) in SQL Server 2000 - a technology I use every other day - so I was most interested in what Peter had to say. It was also the first SIG for this year, and so it's good to get back into SQL Server and hear about the new version (which we are still a fair way away from implementing here at work).

In SSIS, the "package" remains the basic unit of work, but is now an XML file saved with the extension ".dtsx". Peter observed that this aids with source control and is part of the larger move of files not being binary e.g. Reporting Services report files. All "Business Intelligence" development (SSIS, and I'm guessing Analysis Services) now takes place in Visual Studio, or a VS shell if you don't have the full product installed.

Peter's demonstration centred on downloading daily sales zip files via FTP, unzipping them, looping through the unzipped files and reading them into a table, and lastly applying some business rules and populating an aggregate table for reporting (all with only a couple of lines of code). The demo really showed how much thought Microsoft have put into SSIS - I feel like I'm working in the old, wild west with my current set of SQL 2000 tools now!

The two main areas Peter covered under the heading of "Development" were Control Flow and Data Flow. Unlike DTS, any connections in the package now sit in their own tray area (similar to the non-visual components in Windows Forms), and can be re-used anywhere in the package. Their properties (like connection strings) can also be set through variables or config files. In the Control Flow tab, one thing that had definite "cool" appeal for me was the "ForEach" enumerators allowing looping through each file in a folder or each row in a set of records. In the Data Flow tab, the Audit Transformation (where extra columns like machine name, execution GUID, load time could be added to a set of records) and the Condition Transformation looked very impressive.

Peter managed to fit in a lot of new features that had both a "wow!" factor and addressed significant shortcomings of DTS. He presented very well (I don't think he was ruffled by any of the questions from the audience) and covered deployment and management of SSIS packages as well, finding time to demo the logging features, error handlers and config files of SSIS.

I was absolutely tired out by the end of the night and left almost straight away - Darren especially I didn't get to say hello to, sorry mate!

Another good user group meeting, and even though I can't put this technology to use straight away it's still necessary to hear about it and see it demo'd.

Related Posts: February SQL SIG - Overview of SQL Server 2005 Business Intelligence, October Melbourne SQL Server SIG - "Indepth Look at DTS in SQL Server 2005"

Technorati tags: , , ,