Freigeben über

What’s good in SQL Server 2012 Integration Services

  • Artikel

This is a guest post by Allan Mitchell MVP, and one of the top speakers at SQL Bits

 

The launch of SQL Server 2012 is not too far away and the SQL Server team have been hard at work adding new functionality and also improving upon existing features.  I am lucky enough to have been working with SQL Server since version 6.5 and have seen it gradually become the great product that it is today and will be in the future.
My passion has always been around data quality and the movement of data.  Microsoft in SQL Server 7 introduced a new tool called Data Transformation Services (DTS) for which I still have a special place in my heart.  In SQL Server 2005 they completely rewrote this tool and gave it a new name "SQL Server Integration Services (SSIS).  SSIS was a paradigm shift from DTS and took some getting used to.  SSIS in SQL Server 2012 is certainly a release that improves on the existing release and this article will put forwards my two favourite things in SQL Server 2012 SSIS.  Your choices may vary but this is my article and I get to choose my two things ;)

The SSIS Server and Catalog.

At first I did not like this at all.  When I spoke to people like Matt Masson on the development team I was never very positive about it.  Looking back though I think I was being stubborn and not wanting to accept change.  I am quite happy to admit I was wrong.  It is my opinion that if you want to use SSIS in SQL Server 2012 properly and get the most from it then you are going to want to look at the new Project Deployment mode and the SSIS Server.

Using the SSIS Server and catalog gives you a huge amount of functionality including but not limited to;

  • Data Taps: The ability at runtime to "tap" into a part of the data flow and extract the data flowing through.  Incredibly useful for debugging.

 

  • Environments:  Think configurations but better.  You create an environment in which your package will run.  You specify values for parameters and properties in your package and map in them from the environment and off you go. 

 

  • Reporting:  Reporting in a version of SSIS prior to SQL Server 2012 was pretty basic, frustrating and even bordering on pointless.  Not any more.  The team have obviously thought about what we want (and they have been told through Connect).  Now you can get proper SSRS reports showing you phases in your package along with durations and trending of performance over executions. 

WARNING.  If you are an SSRS pro then the reports are functionally very good, aesthetically not so much.  You may also want to look at Jamie Thomson's reporting pack to compliment what comes out of the box

The User Interface.

For a long time now I have been telling anybody who will listen that the weakest part of SSIS is the Business Intelligence Development Studio environment.  Once SSIS is into runtime it is pretty stable and does exactly what it was asked (not necessarily what you wanted, but what you asked).
The SSIS toolbox for example is currently mashed in with the Visual Studio toolbox meaning a whole load of things have to be loaded that have no relevance to the package you are creating
Precedence constraints just disappear from the UI.  They are there underneath the covers you just can't see them.  To this day I have no idea why this happens occasionally but what I do know is that it is almost impossible to develop a package when this happens and you have to restart the package for the designer to redraw the lines.
The UI for developing SSIS packages in SQL Server 2012 has been rewritten in the Windows Presentation Framework.  The edges on the tasks and data flow components have been rounded off resulting in a 10% improvement in speed due to less resistance (only kidding about the speed improvement).  The UI has the look and feel of a proper Visual Studio UI.  The SSIS toolbox lives by itself.  No more loading of not-needed components.  I am very happy with the new UI, very happy indeed.

Summary

This article has covered two of my favourite things coming in SSIS for SQL Server 2012.  By no means are these the only changes.  Go take a look at parameters for instance.  The SSIS team have been hard at work for this release.  There are some things I don't like but there probably always will.  That's why we have Microsoft Connect so we can tell the team about these "opportunities" for improvement.

Go on, give it try and download SQL Server 2012 RC0