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
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.
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.
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.
SQL Server Data Tools has a built-in Package Upgrade Wizard which is launched when a pre-2012 SSIS package is opened.
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.