I’ve started working with SSIS a lot more lately. I am going to attempt to document here some of the quirks that took me a little while to figure out along the way.
In this tidbit, I have a multi-step SSIS package that needs to be run on a daily basis. However, one step of the process should only be run on a specific day of the week (lets say it should only be run on Tuesday).
My screenshots are all taken in Visual Studio 2010. The process is the same for 2008 R2.
The process:
- Create the necessary steps. For this simple example, I created 3 Execute SQL tasks. They each run the query: “SELECT 1”.
- Drag the green arrows to link each of the tasks in order. By default, the links will all be success constraints.
- Double click on the line between Task 1 and Task 2. This will open the Precedence Constraint Editor.
- Change Evaluation operation to: “Expression and Constraint”
- In the expression block, type: “DATEPART( “dw”, getdate()) ==3″
- Using the DATEPART(“dw” ) function, Sunday=1 and Saturday=7. Since we only want Tuesday, we choose ==3.
- Press OK
- You will notice that the line between Task 1 and Task 2 now has an “fx” symbol on top of it.
- At this point, the package will run and Task 2 will only run on the specified day. However there is an issue with the current setup: Task 3 will begin executing as soon as Task 1 completes. It will not wait until Task 2 completes on Tuesdays. We need to modify that link as well to create a fork in the path.
- Double click on the link between Task 1 and Task 3.
- Change Evaluation operation to: “Expression and Constraint”
- In the expression block, type: “DATEPART( “dw”, getdate()) !=3″
- Under multiple constraints, change to the option: “Logical OR. One constraint must evaluate to True”
- Changing to Logical OR is required. Since we created the fork at Task 1, only one of the two lines going into Task 3 will evaluate as successful completion.
- Press OK
- You will notice that the line between Task 1 and Task 3 turns into a dashed line and it also has an “fx” symbol on top of it.
- You can now run your package to test that the steps work properly. To test the design, I changed my formula’s ==3 and !=3 to a different day of the week to make sure that Task 2 got bypassed correctly on its off day.
Very clear and to the point. This was helpful.
Very helpful! Thanks!!
Great! works as outlined, Thank tou
brilliant thank you, just what I was looking for.