Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

Integration Services (SSIS) Package Designing Tips

Posted by denglishbi on February 1, 2009

I recently co-presented at a local event in Bloomington, MN talking about SQL Server Integration Services – Enterprise Manageability.  There was a lot of content to discuss and along the way I mentioned a few items in regards to designing SSIS packages, but I forgot to mention one item in particular – layout.  By package layout I am talking about how the items are placed on the control and data flow along with how they are joined together.

Lets take a look at an example from the Microsoft Data Warehouse Toolkit Book website.  The package that I will use is the DimProduct.dtsx.  Here are a couple of screenshots of the initial control and data flows:

Control Flow

Data Flow

image

image

Look at the item and precedence constraint alignment

Once again, look at the item and data flow alignment

What we see here is that the items that have been placed in the package are not neatly aligned and the precedence  constraints and data flow paths are not nice and straight (when they can be).  If I was creating some documentation or handing this off to a customer I would probably want to take a little time to polish it up and make it look a little cleaner.  Doing this by clicking and dragging the items can be extremely difficult (just like it is in creating a Database Diagram in SQL Server Management Studio).  So how can you do this more effectively?  Easy, enable the ‘Layout’ toolbar within the Business Intelligence Development Studio (BIDS).  You can do this by right-clicking on the tool bar and selecting ‘Layout’ or you can do this through the file menu by selecting View—>Toolbars—>Layout.

image

Now by selecting items within either of the flows I can utilize the different types of options in the ‘Layout’ toolbar to nicely center, top, and middle align the items and make the package layout look like the following:

Control Flow

Data Flow

image

image

Here the items have been center aligned, the annotation is top aligned with the top item, the precedence constraints are nice and straight, and I have enabled the precedence constraint labels.

Once again you can see that the certain items have been center aligned, middle aligned, top aligned, the data flow lines are nice and straight (when they can be), and items have been properly sized to display all of the text (source and annotation items).

This is kind of a nitpicky thing, but now the package looks really clean that the items have been nicely aligned.  Make sure that you properly size items so that you can read the names and annotations properly too.  This makes it much easier to read without having to read properties.  You can also see that in the control flow I have enabled the option to display the precedence constraint labels.  This makes it very easy to see what is going on and helpful for people that have color blindness and have difficulty distinguishing between the green and red lines.  To enable the labels you need to go into the file menu in BIDS (Tools—>Options—>Business Intelligence Designers—>Integration Services Designers) and check the box ‘Show precedence constraint labels’ in the Accessibility section.

image 

In review, here are the items that I would suggest when designing packages:

  1. Enable ‘Layout’ Toolbar
  2. Align items in the control and data flows
  3. Enable ‘Show precedence constraint labels’ (check out the update below in the posting for additional property setting)
  4. Properly size items so that all text is readable

On a side note, effectively naming the items and including annotations are other best practices that should always be included when creating SSIS packages.  I would have included some additional annotations in the package above to add more explanation to what the package is actually doing.  To add annotations you right-click within the control or data flow and select ‘Add Annotation’.  To add additional lines in the annotation perform a CTRL+Enter.  If you want to watch a quick video on documenting SSIS packages take a look at this one by the Midnight DBA – Commenting Tribal Knowledge in SSIS Packages.

Additional design best practices to add to the list above would be:

  1. Effectively name control and data flow items
  2. Include annotations

One last thing…hopefully in the future snap lines will be implemented in the SSIS designer.  It has been part of Visual Studio development environment since Visual Studio 2005 and was implemented in SSRS 2008, so why not across the entire toolset?  Hmmm…

UPDATE (10/17/2009): Jamie Thomson did a recent posting about setting the ShowAnnotation property of the precendence constraints to ‘ConstraintOptions’.  This will show the label and also any expressions that are being used which is very valuable information and improves the self docummenting capabilites of SSIS so that you don’t have to manual enter this information into a annotation.  Check out the posting here Things you might not know about Precedence Constraints : SSIS Nugget.

About these ads

6 Responses to “Integration Services (SSIS) Package Designing Tips”

  1. TJ said

    Another option to quickly organize the Control Flow and/or Data Flow is use Format—>Auto Layout—>Diagram. However, this formatting doesn’t space the items and nicely as you might think so be careful. I like the ‘Show precedence contraint label’ option. Thanks for the tip Dan.T

  2. Jeff B said

    Oh man…the red-green thing never even occurred to me. Good call on even the most basic annotations.

  3. [...] Note: In these screenshots there are labels next to the precedence constraints indicating the type of constraint chosen. This is not a default behavior. To enable this click on Tools menu, go to Options. Under Business Intelligence Designers, go to Integration Services Designers and under the Accessibility section in the General menu, check the box for ‘Show precedence constraint labels’. This is helpful for folks who are color blind and are not able to differentiate between green/red/blue lines in designer. Big thanks to Dan English for this great tip. [...]

  4. [...] Note: In these screenshots there are labels next to the precedence constraints indicating the type of constraint chosen. This is not a default behavior. To enable this click on Tools menu, go to Options. Under Business Intelligence Designers, go to Integration Services Designers and under the Accessibility section in the General menu, check the box for ‘Show precedence constraint labels’. This is helpful for folks who are color blind and are not able to differentiate between green/red/blue lines in designer. Big thanks to Dan English for this great tip. [...]

  5. Nice tricks, didn’t even know it exists after using SSIS for 3 years !!!

  6. [...] Integration Services (SSIS) Package Designing Tips [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 93 other followers

%d bloggers like this: