WISE OWL EXERCISES
SSIS INTEGRATION SERVICES EXERCISES
- Data flow tasks (4)
- Basic data transforms (1)
- Data conversion transforms (2)
- Conditional split transforms (5)
- Lookup transforms (4)
- Looping over files (3)
- Looping over rows (2)
- Merge joins (1)
- Previous versions (18)
- Script tasks (1)
- Variables in script (1)
- Script components (2)
- Accessing file attributes (2)
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.
The answer to the exercise will be included and explained if you attend the relevant Wise Owl course (sadly, only in the UK for now).
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:
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:
|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:
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:
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):
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:
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:
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:
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):
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).