This blog shows a clever way to annotate expression constraints in SSIS
If you've added an expression constraint to control flow in a package in Integration Services, this blog will show you how to display what the constraint does automatically.

Posted by Andy Brown on 27 April 2017

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Automatically Annotating Expression Constraints in SSIS

So thanks to Nick from my course this week for this tip!

The Scenario

Suppose that you have an expression constraint, which will only let SSIS proceed to archive a file if you've imported at least ten rows from it:

Expression constraint

The expression constraint shows up as fx.

Here's what this expression constraint looks like:

The detailed expression

Assume that NumberRows is a variable which holds ... the number of rows imported.

My previous incorrect advice - use annotations

So until this week, I'd thought that this was about the only case in SSIS where it's worth creating an annotation:

An annnotation

You can right-click to add annotations to control flow diagrams, but the annotation won't move as you drag tasks around.

Better advice - change the property of the expression constraint

A better idea is to right-click on the line joining the tasks, to change its properties:

Properties of constraint

Right-click on the joining line, and choose Properties as shown.

Choose one of these options:

Annotation options

See below for the annotation options.

Your choices are:

Option What it will show
AsNeeded Show the fx symbol only.
ConstraintName The Name property (Must have at least 10 rows in the above example).
ConstraintDescription The Description property (hidden in the above diagram).
ConstraintOptions The full expression entered (see example below).
Never Nothing at all.

Here's what you'd see for a couple of choices:

ConstraintName ConstraintOptions
ConstraintName ConstraintOptions

Although the option on the right is dynamic (the package will always display the latest expression), it looks a bit messy!


This blog has 0 threads Add post