SQL Server 2012 Denali SSIS Enhancement - Group and Connection Managers

SQL Server 2012 - Denali SSIS Enhancement

Hello!

We at SQLServerGeeks.com are committed to spread the knowledge as we gain in the process of evaluating the newly released SQL Server 2012 CTP 3 release code named “Denali”.

This post comes as a supplementary addition to the earlier post which described one of the SQL Server T-SQL Enhancement

In this post we shall preview Group and Connection Managers

Group

This is one of many new features added to the latest community release of SSIS.

Consider a scenario as below:

I have couple of EST (Execute SQL Task) and a Sequence Task in Control Flow. This can be clubbed as shown in the graphic below:

That looks all very nice and ordered. I can set properties for the entire container. To mention few Edit Transaction properties, toggle the disable flag. Any change made to the new host container would be then propagated (of course with certain exceptions) to all child tasks in the container. This is the Control Flow pane we are talking about.

But, till the SQL Server 2008R2 release there was no such provision to group tasks in Data Flow. There was no way of group a collection of tasks.  So the Designer Pane would resemble to the graphic below:

With Denali, we can now group a collection of tasks in Data Flow.

Well go on read the steps below. This is fairly simply and a very rich feature.

Step 1: Select the collection of tasks you wish to group. As shown in the graphic below

Step 2: Click on the grouping option and the selection is grouped in a new host container. As shown in the graphic below

Take away:

You cannot change any properties of the new Group container introduced. It is just made available for purely to enrich the aesthetic properties of the designer window.

Connection Managers

Another change that you should notice is that in the Solution Explorer no longer you will see the Data Sources and Data Source Views. That is because; the existing features have been replaced by Connection Managers and if you were to take my word, this again is one of the better and much wanted change that was required as far as package development is concerned.

With SQL Server 2012 Denali, one can create connection managers either at Package Level or at Project Level.

You may interpret the above statement as local connection manager – which means that the connection manager will be made available exclusively for the package for which it is created.

So, if I were to create a new Excel Connection manager to load an Excel file into my SSIS package and I am sure that the same file would not b e used anywhere else in the project, I would go ahead create a new package level or a local connection manager.

Now if you create a new package you will not see the Excel connection manager. No rocket science. The usual behavior.

But, what if I were to use the same file connection to perform some different kind of transformation in some other SSIS package. The way out would be to create a new connection manager and access the file.

Now with the new SQL Server 2012 Denali release, this is no longer required. The user is empowered to create project level connection managers, just like global variable, which are made available to objects in the solution.

So, if there are connection managers, which are to be used in multiple packages, instead of creating one for each package I can now create one single project connection manager.

I hope this post helped you. Happy learning!!