Jan 162013

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.