Integration Services (SSIS) Package Designing Tips

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

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

 

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