RDA Corporation

Blog

Overview of New Features in SSIS 2012

By Ray Barley on April 19th, 2012 // No Comments

SSIS_2011

Since SQL Server Integration Services (SSIS) was released with SQL Server 2005, there have been a variety of issues with the developer experience, deploying packages, and executing packages. SSIS 2012 has addressed a number of the key pain points, particularly in the area of deploying and executing packages.

What’s New in SSIS 2012

There are a number of new and useful features in SSIS 2012. They can be broken down into the following categories:

  • Developer experience
  • Deployment model
  • Package execution
  • Package upgrade

Developer Experience

SSIS packages are designed using SQL Server Data Tools which is based on the Visual Studio 2010 shell and replaces Business Intelligence Developer Studio (BIDS). This provides an up-to-date user interface, including the following:

  • A WPF-based designer
  • Zoom in and zoom out
  • VSTA 3.0
  • .NET 4.0
  • New icons
  • A new toolbox that includes new tasks such as Change Data Capture (CDC) Source, CDC Splitter, and Data Quality Services (DQS) Cleansing.

The new release has some features geared to cutting down on the learning curve. For example, the Source Assistant and the Destination Assistant are available for configuring data sources. Connection managers can now be defined at the project level and shared between multiple packages.

Deployment Model

The new deployment model is similar to SQL Server deployment available in previous versions but with some very interesting new capabilities. Deployment is now project-based instead of SSIS package-based. Building a project creates an .ISPAC file that contains everything needed for deployment. Deployment can be done via the Deployment Wizard or PowerShell.

Since the entire SSIS project is deployed as a single unit, permissions can be set at the folder and project levels. Groups of variables can be defined in a container called an environment and the environment can be chosen at runtime. While the new project deployment is the default, the current package deployment model is still available.

Package Execution

Parameters can now be mapped to variables in an environment and the environment can be specified at runtime. Since all packages in a project can be deployed as a single unit, the Execute Package task no longer requires a connection manager.

Package Upgrade

SQL Server Data Tools has a built-in Package Upgrade Wizard which is launched when a pre-2012 SSIS package is opened.

Conclusion

SSIS 2012 offers much-needed improvements in the areas of developer experience, deployment and execution. You should consider using SSIS 2012 as soon as possible to build packages more efficiently, and take advantage of the improved deployment and execution models.

About the Author: Ray Barley is a Principal Architect with RDA. He has been a Microsoft technology specialist for 15+ years, designing and developing custom business applications, data warehouses and business intelligence solutions. He has played a key role in helping organizations understand the Microsoft platform and how to best leverage it. He has the MCSD, MCSE and MCDBA Microsoft certifications. Recently, Ray helped to co-author an Unleashed series book for Business Intelligence in SharePoint 2010 detailing the different BI components that work with SharePoint 2010: PerformancePoint Services, Reporting Services, PowerPivot, Visio Services and Business Intelligence Center.