The Baker’s Dozen Doubleheader: 26 new Features in SQL Server Integration Services 2012 (Part 2 of 2)
In the first game of this doubleheader (the last issue of CODE Magazine), I covered 13 new database and T-SQL features in SQL Server 2012. Well, it’s the second game of the doubleheader, and the nightcap features 13 new features in SQL Server Integration Services 2012. SSIS has always been a good time, and now it’s an even better tool with enhancements and improvements over prior versions. Even if you had a love/hate relationship with SSIS before, you’ll find that Microsoft paid special attention to SSIS 2012. Starting Lineup for Game 2 Normally in Baker’s Dozen tradition, I say, “What’s on the menu?” This time, I’m saying, “The starting lineup is as follows:” - New Development Editor - use of SQL Server Data Tools 2010
- New Shared Connection managers to simplify the connection manager process across packages in a project
- SSIS parameters at the project, package, and task level
- Baker’s Dozen Spotlight: a new variable expression task, to eliminate instances where scripts are necessary
- New UNDO/REDO functionality in the data flow editor
- New SSIS Expression Language Features
- New native ODBC Data Flow Source and Destination Components
- Greatly improved recovery from data lineage and invalid metadata reference issues
- New Data Taps functionality to programmatically “tap” into a Data Flow pipeline
- New SSIS tasks to support Change Data Capture
- New Deployment features in SSIS 2012
- A new SSIS Server Management Dashboard feature
- The Baker’s Dozen Potpourri - Miscellaneous new features in SSIS 2012
SQL Server 2012 Released to Manufacturing! As of this writing (early March 2012), Microsoft has released SQL Server 2012 to manufacturing, and has set a release date of early April 2012 for general availability. With the new column store index, T-SQL features, SSIS features that I’ll talk about in this article, and new Business Intelligence features that I’ll talk about in subsequent articles, SQL Server 2012 is an industry game changer! Tip 1: New SSIS Development Environment using SQL Server Data Tools Prior to SSIS 2012, SSIS developers used Business Intelligence Development Studio, which was a shell of Visual Studio 2008. Some who used SSIS 2008R2 were (understandably) upset that even the R2 version (released in 2010) still used the VS2008 shell, as opposed to the updated WPF-based Visual Studio 2010 shell. Fortunately, the “planets now align” - SSIS 2012 uses the WPF-based Visual Studio 2010 shell. The SSIS development editor is much more visually appealing. Although this might not be critical for experienced ETL developers, a better looking UI will help with the appeal for new ETL developers. Figure 1 and Figure 2 show the control flow and data flow for an SSIS package in the new SSDT environment. (At the end of this article, I’ll talk about what this package does.) 
Figure 1: The control flow for an SSIS package in the new SSDT environment uses the WPF framework.
Figure 2: The data flow for an SSIS package in the new SSDT environment has the pipeline in blue instead of the old green color. | & | | 
By: Kevin S Goff Kevin S. Goff, a Microsoft SQL Server MVP, has been writing “The Baker’s Dozen” productivity series in CoDe Magazine since 2004. Kevin hosts a weekly webcast on SQL Server and Business Intelligence topics, at http://www.BDBIRadio.com.
Kevin has been an applications developer, architect, and technology mentor since 1987. Kevin has worked in many lines of business, including insurance, manufacturing, health care, consumer packaged goods, accounting and finance, advertising, and many others. Along the way, Kevin has won several awards in both the public and private sector for applications development. Additionally, Kevin has taught SQL Server, Business Intelligence, and Data Warehousing classes to hundreds of students over a period of five years and has built numerous custom courseware modules along the way.
Kevin has authored one book on reporting development and contributed chapters on MDX programming to a second book. He is currently developing a set of commercial video training courses for SQL Server and BI topics. He has been a SQL Server MVP since 2010 and was previously a .NET/C# MVP from 2005 to 2009.
Kevin is a frequent speaker at User Group and other community events (SQL Saturday, SharePoint Saturday, Code Camp) in the Mid-Atlantic region, and speaks occasionally at conferences. In 2012 Kevin worked with Microsoft TechNet Radio on a 13 week webcast series on new features in SQL Server 2012.
For more information, check out Kevin’s main site at http://www.KevinSGoff.net, or email him at kgoff@kevinsgoff.net
kgoff@commongroundsolutions.net |