SSIS - What’s New in SQL Server Denali

What’s new in SSIS? In a word: Lots.

It’s been a lot of fun showing off the new SSIS features and functionality – the hardest part has been fitting it all into a 60 minute timeslot. This CTP is close to feature complete – the rest of the time we have for the Denali release will (mostly) be spent improving and polishing what’s there. However, you can expect a couple of new features to slip in for the final release - we have to leave some surprises for RTM, right?

There have been two major themes for SSIS in the Denali release – improving the Developer Experience, and making SSIS solutions easier to Deploy, Configure, and Manage.

Developer Experience

We wanted to make the designer easier to use, as well as deliver productivity enhancements to delight our existing users. Some of the major features here include:

• Visual Studio 2010
• New VSTA scripting environment (also based on VS 2010)
• Undo/Redo
• Shared Connection Managers
• Icon marker (“Adorner”) to indicate Connection Managers and Variables with expressions
• New SSIS Toolbox
• “Getting Started” window with links to samples and videos
• Variable window improvements
  • Variables created at the package scope by default
  • Button which allows you to move variables to new scopes
• New data flow column mapper
• Improved performance when loading packages in the designer
• Simplified data viewers
• New package format makes it easier to diff changes
• Data Quality Services Cleansing transform

Deployment & Configuration

The deployment and configuration improvements revolve around the new SSIS Catalog (or SSIS Server). This catalog is essentially a SQL application - a user database on a SQL instance (SSISDB) with a set of stored procedures and a T-SQL API. In addition to the server, we’ve introduced a new Project model for bundling your packages together to simplify deployment, and a new Parameter model to make configuration and management a little more straight forward. This CTP contains a number of enhancements to what was delivered in the previous CTP, as well as a bunch of new features, including:

• Automatic capture of package execution logs
• Various logging improvements
• Built-in reporting
  • Integration Services dashboard – see all packages that have run on the server in the past 24 hours
  • Performance reports – see a package’s performance over time
  • Error message report – shows failed package executions and related error messages
  • … and more!
• Data tap functionality – dynamically capture/log data as it flows through the package (without modifying your package)
• Create SSIS Catalogs on remote machines via SSMS
• Connection Manager properties are automatically exposed on the server

CTP Improvements

We have made further improvements to the new functionality that appeared in the previous Denali CTP – thank you all for your feedback!

• Improved Deployment and Migration Wizards
• Project parameters now have their own node in the Solution Explorer
• Package parameters have their own UI
• Dependency Services feature (also known as Impact Analysis & Lineage) is now Project Barcelona
• Removed the Data Sources folder (support for .ds files)
• Added “Parameterize” option on the right-click context menu for Tasks and Variables
• Ability to control logging level on the server
• SSMS manageability improvements

Other Changes

In addition to the major themes, we’ve made many small enhancements and quality improvements through out the product. Some of the more noticeable ones include:

• Annotations auto-grow, wrap when you hit enter, and are persisted as clear-text
• Auto-save and recovery in BIDS
• Expression improvements
  • 4000 character limit is removed
  • New functions – LEFT, TOKEN, and TOKENCOUNT
• Connection Managers validate in parallel
  • Can be taken “offline” individually
• Better error messages for the Execute SQL Task
• Option to allow automatic type conversion in the Execute SQL Task parameters and result values
• Flat file source supports embedded qualifiers and a variable number of columns per row
• Raw Files now include sort information
• New Expression Task
• Scripting engine improvements

Here are some additional links if you’re looking for more information.

• What’s New in “Denali” for SSIS from TechEd 2011
• SQL Server Denali launch page
• Data Quality Services presentation from TechEd 2011
• CTP3 Download
• Books Online