SQL Server 2005 gives us loads of flexibility in our workflow management to decide how and if the following task should be executed. In this article we are going to introduce you to one of those ways and that is putting an expression into the workflow constraint itself.
The Situation is as follows:
We have processes that run every week day night and those processes are different based on the day of the week. What we want to do is build one package whereby we calculate what day of the week it is and our package flows through the correct steps for that particular day.
Here is how our package will look at completion
Let's just take a moment to explain what we see. We have a Script task which is joined by Success (green) constraints to 5 sequence containers. Next to each green constraint is an icon showing fx which is the key to this whole thing.
The first thing we need to do in our package is create a new variable so let's go there and create our variable and name it DayOfWeek
This variable will be used later in our expressions. We now need a way to set the value of the variable dynamically and for that we are going to use the Script task. Drag one onto designer from the toolbox and double click on the task. Select Script from the left hand side and then click the Design Script button on the right hand side. There will be a section in there that tells you where to write your code. Here is the code we use.
Public Sub Main()
Dim vs As Variables
'We need to lock the variables so we can read it without anything else changing it
'Assign it a value
vs.Item("DayOfWeek").Value = System.DateTime.Now.DayOfWeek.ToString
'remember to unlock the variable now
Dts.TaskResult = Dts.Results.Success
Once we have the Script task on the designer we then add 5 sequence containers. These are as the name suggests a wonderful place in which to put a sequence of tasks you want to execute. For us they would hold any tasks that were to be executed on the relevant day of the week. We have labelled them according to the day of the week as well.
We now join them to the Script task by means of the Green constraints that appear from the bottom of the Script task. Presently if we were to execute this package as is then regardless of what day of the week it was we would execute all our containers which is not what we want so we need a way of controlling that and here is where our expression comes in.
Double click on the green line between the Script task and the Sequence Monday sequence container. You will see similar to the following graphic.
There are two things of interest to us here and that is Evaluation Operation and Expression. Have a look around and see the combinations you can have. For our example though all we need is to have the constraint be processed because of an expression and that expression is the evaluation of whether our variable equals our chosen day of the week. You would repeat this process for each Workflow constraint which in our case is 5.