December 2011

You are currently browsing the monthly archive for December 2011.

When I opened an existing SSIS project in the new SQL Server 2012 RC0, I came to an interesting discovery: an empty Toolbox pane!  Even with an SSIS package open in the designer.  Hmm, that’s funny!  So where are my SSIS components?

Take a good look at the following screenshot:

The Toolbox is no longer the SSIS Toolbox but the new SSIS Toolbox is!

That’s right, they are not in the Toolbox anymore but in the SSIS Toolbox instead.  This new toolbox is a bit different from the old one.  Besides the grouping of components that has changed, the most important change is that it will automatically detect any custom components.  You no longer need to right-click, select Choose Items, go fetch a coffee, wait until it cools down a bit, drink it and finally … select your custom component.  No, you’ll have to find another reason to get that coffee shot.  Actually, that’s not entirely true: you still need to right-click and then click Refresh Toolbox and then the custom components will be shown.

Another difference is that it’s split in two parts.  The bottom half of the pane now contains a description of the selected item, including a link that should lead to samples and a link to the Books Online.

The new SSIS Toolbox shows a description of the selected=

Out of curiosity I tried the Find Samples link a couple of times, but for now it doesn’t seem to deliver much content:

Not many results through Find Samples link

Okay, so one thing remains: how do you open the new SSIS Toolbox pane?  According to the Books Online it should be opened automatically when you open an existing project.  Well, apparently not all the time!

The first place I’d look is in the View menu.  But alas, SSIS Toolbox is not one of the menu items.  Not even in the Other Windows submenu.  Why oh why?!

Long story short: do you see those two buttons in the below screenshot?  They’re new!

Package designer has gotten two new buttons

The first button leads to the Variables pane, the second button will open the SSIS Toolbox.  Good to know isn’t it?!

Further investigation led me to the following: according to the Books Online, the SSIS Toolbox item should actually be located in the View > Other Windows menu.  As that is not the case and I think it’s only logical to have that pane added to the View menu as well, I’ve filed a bug on Microsoft Connect.  Go ahead and vote!

Have fun!

Valentino.

References

SSIS Toolbox

Share

Tags: , ,

A couple of days ago I came across a funny and weird object in the Control Flow.  It looked like this:

Group Tasks

As you can see, this container looks a bit similar to the Sequence Container but the Sequence Container has a different icon.  Also, this container does not have any connectors sticking out.  I’m currently involved in a 2005 > 2008 migration project, which is how I came across this container in the first place, and thus I really needed to find out what this object actually is.  So I started to investigate.  Unfortunately the properties were not very helpful:

Group does not have any properties

No properties, nothing at all, not even an object name.  Then I started to scan the Toolbox pane for the icon.  Guess what?  Right, nothing either!  Is this some kind of custom control??

The logical next step is to perform some internet searches and I found the answer: this is standard SSIS functionality that exists since SQL Server 2005!  WTF! (is what I thought at that moment)

The Grouping Container

In the Control Flow, if you right-click on at least one task, you get the following options:

image

And when you select Group, you’ll get that group container around the selected objects, allowing you to collapse the items.  Well, okay, I’ll probably still never use it now that I know that it exists.  Which is possibly the reason that I don’t know about it in the first place.

How Not To Use It

Using that grouping functionality you can end up with some weird-looking flows:

How not to use the group tasks functionality

What have we learned?  That even with years of SSIS experience, it’s still possible to discover “new” functionality.

And speaking of new functionality, that brings us to SQL Server 2012 (formerly known as Denali).

Grouping In SQL Server 2012

As of SQL Server 2012, not only will you be able to group tasks in the Control Flow.  You can also group components in the Data Flow.  Now that may prove more interesting than its Control Flow counterpart.  Why?  Because in the Control Flow chances are that you’ve already grouped your tasks using some Sequence Containers while you don’t have any containers in the Data Flow.

Here’s what it looks like:

SQL Server 2012 allows grouping components in the Data Flow

As you can see, the icon is now gone.  But the properties pane is still totally empty with the group control selected.  Oh well…

Have fun!

Valentino.

References

How to: Group Tasks and Containers in a Control Flow

Share

Tags: ,

© 2008-2017 BI: Beer Intelligence? All Rights Reserved