SSIS Integration Services | Previous versions exercise | Demonstrate how task constraints work

This exercise is provided to allow potential course delegates to choose the correct Wise Owl Microsoft training course, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.

Software ==> SSIS Integration Services  (46 exercises)
Version ==> SSIS 2012 and later
Topic ==> Previous versions  (18 exercises)
Level ==> Relatively easy
Subject ==> SSIS training

You need a minimum screen resolution of about 700 pixels width to see our exercises. 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.

The aim of this exercise is to create the following package:

Package to demonstrate constraints

There are two possible paths through this package, as explained below.

The idea is that when you run this package, you'll see one of two possible messages:

Message if all conditions true Message if conditions not true
If the conditions are all true  ... ... or otherwise.

To get this to work, first create a package called Random joy.  Within this create 4 variables as shown below:

Four variables

One boolean (yes/no) variable and 3 integer ones!


The idea is that we'll set r1, r2 and r3 to pseudo-random single-digit numbers, then display the success message if and only if all of these numbers are less than 5; otherwise we'll display a failure message.

Theoretically this will lead to the success message displaying one out of eight times; in practice, limitations of our random number generation will mean we'll see it nearly a third of the time.

Create a sequence container, and within this put 3 expression tasks:

Container with expression tasks

Each of the expression tasks should work in a similar way, as below.

Here's what the first expression task should set (the others will be similar):

@[User::r1] = DATEPART( "ms", GETDATE() ) % 10

This takes the number of milliseconds at this instant, divides it by 10 and gives the remainder.  Theoretically this should give a more-or-less random number; in practice, it seems to always return 0, 3 or 7, for reasons unknown!

Now link these expression tasks up to a script task, with expression constaints on each path that the random number (r1, r2 or r3) should be less than 5:

Random number task

The message will display only when all 3 random numbers are less than 5.


Add an expression task which sets the IfShownMessage variable to True:

Recording message displayed

Set the value of the IfShownMessage variable to True.


Add in another script to display a failure message, and execute this only when the IfShownMessage variable is False:

Final message

Be very careful how many = signs you include in the expression constraint for this final task!


Run your package a few times - it should give you one message or the other, depending on the values for the random numbers.

You may find it useful to set a breakpoint for the sequence container's PostExecute task, so that you can monitor the values of the variables in a Watch window.

Finally, amend your package so that it displays the message of joy if ANY of the 3 random numbers is less than 5 (you should only have to edit one of the flow lines):

Any condition can be true

Now any of the 3 conditions can be true.


Run your package again to see that you get the message of joy significantly more often (nearly always, in fact).

You can unzip this file to see the answers to this exercise, although please remember this is for your personal use only.
This page has 0 threads Add post