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:

1_SQL_Server2012_Denali_SSIS_Enhancement_Group_and_Connection_Managers

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:

2_SQL_Server2012_Denali_SSIS_Enhancement_Group_and_Connection_Managers

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

3_SQL_Server2012_Denali_SSIS_Enhancement_Group_and_Connection_Managers

   

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

4_SQL_Server2012_Denali_SSIS_Enhancement_Group_and_Connection_Managers

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.

5_SQL_Server2012_Denali_SSIS_Enhancement_Group_and_Connection_Managers

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!!

 

Regards

Raunak Jhawar

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook

   

7 Comments on “SQL Server 2012: Denali SSIS Enhancement – Group and Connection Managers”

  1. This was pretty cool…however I am facing issues creating an OLEDB connection manager in the solution explorer. It says the .conmgr is not a valid stream name in the project.

  2. Hey, thanks for visiting the blog.

    I am not able to replicate the error. Anyways, the best approach is to create a connection manager from the connection area provided at the connection managers tab and then convert it to Project connection(as in SQL Server 2012, new feature. Also any renaming if required can be made in this area)

    Raunak

  3. I have managed to replicate the issue. Avoid using special characters(\,*,$ etc) while converting a package connection to project connection.

    For example: Instead of <>\<>.conmgr rename the connection to some user friendly name as <>. This should work as a project connection since the new name has no “\”

    Thanks\Raunak

  4. Hi…thanks for the solution…however the name was the default given by SQL Server. I tried changing the name as well but it just restarted the development studio. When I reopen the solution, I could see many connections created with that name with a suffix of 1,2,3 etc. Really not getting what could be the issue.

  5. Hi…I tried some more options…what you were saying is correct. First creating a package connection and then changing it to a project connection. But if I try to directly create a project connection, it gives me an error. This could be a bug with SQL 2012.

Leave a Reply

Your email address will not be published.