SSIS package run on a specific day of the week
We 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.
Drag the green arrows to link each of (Copy Folder ,Delete Folder ). By default, the links will all be success constraints.
Double click on the line between (Copy Folder ,Delete Folder ).
This will open the Precedence Constraint Editor.
Change Evaluation operation to: “Expression and Constraint”
In the expression block, type: DATEPART( "dw", getdate()) ==5
You will notice that the line between (Copy Folder ,Delete Folder ).
Now has an “fx” symbol on it.
At this point, the package will run and Copy Task will only run on the specified day.
Double click on the link between (Create Folder ,Copy Folder ).
Change Evaluation operation to: “Expression and Constraint”
In the expression block, type: DATEPART( "dw", getdate()) !=5
Under multiple constraints, change to the option:
You will notice that the line between (Create Folder ,Copy Folder ) turns into a dashed line and it also has an “fx” symbol on it.
Now run your package or press (F5) to test if the steps work properly
This solution is executed using SQL Server Data Tools
Create the necessary steps. For this simple example, I created 3 File System Tasks.Create Folder
Copy Folder
Delete Folder
Double click on the line between (Copy Folder ,Delete Folder ).
This will open the Precedence Constraint Editor.
Change Evaluation operation to: “Expression and Constraint”
In the expression block, type: DATEPART( "dw", getdate()) ==5
Using the DATEPART("dw") function, Sunday=1 and Saturday=7.Press OK
Since we only want Thursday, we choose ==5.
You will notice that the line between (Copy Folder ,Delete Folder ).
Now has an “fx” symbol on it.
At this point, the package will run and Copy Task will only run on the specified day.
Double click on the link between (Create Folder ,Copy Folder ).
Change Evaluation operation to: “Expression and Constraint”
In the expression block, type: DATEPART( "dw", getdate()) !=5
Under multiple constraints, change to the option:
“Logical OR. One constraint must evaluate to True”Press OK
Changing to Logical OR is required.
Post a Comment