MSDN Webcast Summary: Introducing SQL Server Integration Services for SQL Server 2005
By Stephen Wynkoop and Sean Maloney
This article is a summary of the webcast that provided details about the SQL Server Integration Services for SQL Server 2005 (formerly Yukon). The webcast was presented by Kamal Hathi, Product Unit Manager for SQL Server Integration Services.
Note: please see also the SQL Server 2005 Integration Services webcast presented by SSWUG.ORG – http://www.sswug.org/webcasts - check the archived webcasts. Please also see the editorial that followed that webcast for additional thoughts.
One might think of the new DTS - SQL Server Integration Services or SSIS as "The Visual Studio interface, an API and new features come to the old DTS." A big point that was made at the start of the webcast though was that SSIS is not a direct replacement of DTS – in fact there is an alternate DTS engine that will run your existing jobs. You’ll need to upgrade and replace those jobs with SSIS in order to use the abilities in the new environment. SSIS is really such a different paradigm
Data Flow Development Tools
Many of the things one would expect from such a change are realized nicely in Integration Services, for example it is now possible to debug packages and set watches. Some additional advantages include support for differently scoped variables, and obtaining source configuration settings from XML files.
A rather nice feature was the addition of “data views” that can be used for debugging purposes as you’re building data flows. These data views let you step into the data flow process and see what’s in the queue at that point – you can then make adjustments and see the impact of those adjustments on the data. Very nice debugging and testing tool.
The real meat of the webcast was the demonstrations of transformation capabilities, for which many new objects are included by default. Some interesting points mentioned include:
- Any managed .Net language may be used to create a custom transformation (which less knowledgeable users may take advantage of using the SSIS interface)
- An interesting "slowly changing dimension object" was mentioned and shown in the designer in terms of the object and it’s associated set up wizard
- RSS Feeds as sources and destinations were shown being processed directly by SSIS – and dropped into a database of “interesting” content or discarded based on content
SSIS As A Regulatory Tool?
Interestingly, regulatory issues, which are a significant concern for many enterprises of late, were mentioned (SOX, Basel) and it was claimed that SSIS provides sufficient performance and reliability to allow for the creation of "seamless manageable" operations to deal with the some of these issues.
To justify the assertion it was noted that SSIS is memory resident - and therefore high performance. But this means SSIS likely is also potentially quite resource costly, and one must therefore wonder if SSIS is a fully ACID compliant functionality, and what happens if the resources run out. The latter an especially open question as SSIS was said to run in association with the SS Agent - and not as a separate service itself.
It was not really made clear how IS really addresses management and auditing well. However, it was noted that audit data may be directed to database, file or other sources and that packages may now be encrypted (probably supported with the new built in SS encryption capabilities). Later in the webcast a B&N and SQL Server Case study was described featuring an implementation of the "slowly changing dimension transformation object".
Summary
All in all the old DTS was a good if somewhat limited ETL tool; now it rather appears that the increased support and improvements in DTS / SSIS may give a number of ETL oriented vendors (for example Ab Initio) quite a bit more to be concerned about.
SSIS is one of the many tools in the new SS2005 suite that will take some learning. The very slick editors and debugging steps that have been created will really be nice when building out data flows.
There is also a good deal of logging done in the tool – making it possible to almost see the decisions and processing that were completed.
