Our Blog

where we write about the things we love

11

Apr

Improvements to SQL Server Integration Services in SQL Server 2012

SQL Server Integration Services (SSIS) is another aspect of SQL Server that has been overhauled in SQL Server 2012.

Because SSIS is a development tool, and the updates are mostly of a technical nature, trying to explain their business value is quite challenging. Putting it simply, the main value to business is that with the updates, development will be easier and therefore faster.

I will focus on a few of the development improvements about which I’m the most excited.

Visual Studio 2010

Business Intelligence Development Studio (BIDS) has been replaced with SQL Server Data Tools, which uses the core of Visual Studio 2010. This does not just apply to SSIS but the whole BI development environment. This is due to Microsoft’s internal realignment of their internal product delivery cycles which should help reduce the mismatch between functionality in related tools. This makes deployments much simpler and integration with Team Foundation Server 2010 a lot smoother.

Ability to debug Script Tasks

In previous versions of SQL Server, you had the ability to debug Script Components but not Script Tasks. With the release of SQL Server 2012, this is no longer the case: you can forget about having to output to the console to try and figure out where exactly your code is failing.

Change Data Capture

Although Change Data Capture (CDC) is not is not new to SQL Server, there are now CDC Tasks and Components within SSIS that make it easier to implement.

Undo and Redo

At long last you are now able to undo or redo any actions – such as bringing back the data flow that you accidently deleted – without having to reload the whole project. In my opinion this improvement alone makes it worth upgrading!

Flat File Source Improvements

Two great additions to SQL Server 2012 that will solve a lot of headaches when importing data from flat files are the support for varying numbers of columns and embedded text qualifiers.

Project Connection Managers

Gone are the days where you had to recreate connections to your source and destination within each SSIS package. Connections can now be set up at a project level which can then be shared within the packages.

Column Mappings

In SQL Server 2012, SSIS is a lot smarter about how it deals with column mappings and now uses the column names instead of the lineage ID. This means that if you decide to recreate your data source task, you do not have to remap all the columns as was the case in the past. SQL Server 2012 also comes with a Resolve Column Reference Editor which allows you to link unmapped output columns to unmapped input columns across the whole execution tree; in the past this had to be done from task to task.

Parameter Handling

Parameters are a new addition to SSIS and are very useful. In the past you had to use configurations which could only be assigned at a package level. Parameters can now be set at both a package and project level. You can assign three different types of values to parameters, namely Design default, Server default and Execution.

There are quite a few more additions to SSIS (including its built-in reporting capabilities, improvements to the user interface, and integration with Data Quality Services), but the features I have focused on in this post are improvements to issues that I have frequently come across on previous projects. I’m sure these improvements and additions to SSIS will be greatly appreciated by the industry.

Posted by: Dylan Venter, Senior Consultant, Enterprise Applications | 11 April 2012

Tags: Business Intelligence, SQL Server 2012


Blog archive

Stay up to date with all insights from the Intergen blog