ALL SOFTWARE EXERCISES

- EXERCISES HOME PAGE (827)
- Access 2010 (66)
- Access VBA Macros (17)
- Advanced VBA (29)
- Excel 2010 (83)
- Excel 2013 (10)
- Excel 2016 (0)
- Excel VBA Macros (37)
- Power BI - Excel 2013 (25)
- Power BI - Excel 2016 (23)
- Power BI Desktop (26)
- PowerPivot 2010 (26)
- Report Builder 3.0 (42)
- SQL (156)
- SSAS - multidimensional (21)
- SSAS - tabular (29)
- SSIS Integration Services (40)
- SSRS 2016 (14)
- SSRS Reporting Services (53)
- Visual Basic (45)
- Visual C Sharp (65)
- WPF - Visual C# (20)

SSIS INTEGRATION SERVICES EXERCISES

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

Category ==> | SSIS Integration Services (40 exercises) |

Topic ==> | Previous versions (18 exercises) |

Level ==> | Relatively easy |

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):

**@[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:

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