Blog Post

SSIS Data Flow "Sequence Containers" are Here!



OK - maybe too much of a celebration... and I do have meatier topics in the article pipeline.  However, I just stumbled across a feature in SSIS that I didn't realize had been included - due to a Connect item I'd raised a long time ago (in early 2009).  How come I didn't know that the SSIS team had fixed this in 2010?  Because I was stuck on 2008R2!  But this is something I've been wishing for forever - and all of you should be using this!

Data Flow Sequence Containers

OK - so what is a "Data Flow Sequence Container"?  Back when I started using SSIS 2008R2, I'd just come from a coding background, and really appreciated the existence of #region/#endregion in C#. 

Summary level region

I used regions before I started actually coding something to write pseudo-code as the regions, drill down into each "step", perhaps generate sub-step regions, until I got to "code level".  This use of regions basically replaced my use of code comments (I still do use some).  It was so much easier to describe what and why a chunk of code was doing, while hiding the code.  Regions permitted much clearer exposition of what was going on in the code at a high-level, and letting the developer dig in as much or as little as they wanted. 

One level lower detail regions

It facilitated debugging and code understanding by walking the unfamiliar developer through the thought process behind the code, instead of having the developer try to surmise what and why code was there (with comments).  With only comments, the developer has to try to keep multiple ideas/steps in his head while scrolling through code.

How are regions not awesome?

If you're not using these in SSIS Scripts, you SHOULD BE.
What I asked for in "Data Flow Sequence Containers" is the SSIS equivalent of this, and it's here.  (Actually must have been here since SSIS 2012.  Hi.  I'm Todd, and I'm new here.)

Data Flow Groups

Data Flow Groups is what they're calling it, and it's deceptively simple to use.  One of the reasons I'm sure I (and SSIS people I talk to who DID NOT LET ME KNOW IT WAS THERE) missed it is because I was expecting it to be a component in the toolbox.  Not so.
Code up your Data Flow as you normally would.  Then go and select the components that you want to group together - via clicking and dragging a selection window, or click-selecting components.  Any component combinations you want.  Then right-click and select Group.

Collapse Groups

Just like code regions, you can collapse groups to make it easier to see the "big picture" in a sea of components.

Name Your Groups

To make your groups easier to understand, name them.  Doing so is better than annotations (at least for short comments) because the name is visible when the group is expanded or collapsed.  If you have more to say, put an annotation inside the Group - and it will be invisible when collapsed, decluttering the data flow, until you need it.

Nesting is Supported

Just like my lovely C# code regions, you can nest groups in SSIS!  Take developers from the highest-level of abstraction to the nitty-gritty details in however many steps makes sense.

Original post (opens in new tab)


You rated this post out of 5. Change rating




You rated this post out of 5. Change rating