4

I have an SSIS Package that is scheduled to export data on a daily basis. I have a few Tasks that I'd like to execute only on the first day of the month. It is important that these Tasks are skipped, and that the Package continue to execute if it is not the first of the month.

After doing a bit of searching, I've found numerous references to solve this problem by using the method posted in the following blog post: http://bi-polar23.blogspot.com/2007/05/conditional-task-execution.html

I think, however, there is a simpler solution. Why can't I just add an expression to each of my 1st-day-of-the-month Tasks which checks the day of the month and sets the "Disabled" property on the Task accordingly?

Using expressions and disabling the Task seems a lot simpler than adding a dummy script before each Task. Does anyone else see or know of a problem with my method for solving this problem? Preliminary tests appear to work, however, I've found SSIS to be finicky. I'd rather not do something that is "unconventional" if it might cause problems in the future.

Thanks for any thoughts.

RLH
  • 15,230
  • 22
  • 98
  • 182

1 Answers1

9

I don't think there is anything unconventional about setting expressions to disable tasks on conditional basis. I have done the same thing in a package that I developed. I would prefer to set the conditions directly on the Disable property on the task using Expressions rather than using Script Task. Refer screenshot #1. In other words, I agree with your approach.

A note of caution with this approach would be that the next person looking into the package may not be aware why a particular task didn't execute on a given date (in your case first day of the month). To prevent that, you can add an Annotation stating that there is an Expression set on the Disable property of the task.

One other thing I would suggest is to calculate the first day of the month value and store it in a variable that has the property EvaluateAsExpression set to true and use this variable in the Expressions of a given task to disable\enable it. This would prevent calculating the value in each of the tasks' expressions. Screenshot #1 displays that the Disable property is set to assume the value stored in the variable IsFirstDayOfMonth. Expression on this variable can be set to calculate the first day of the month.

Hope that helps.

Screenshot #1:

1

  • Excellent! Your response is exactly what I was planning on doing. I didn't add the bit about setting an expression variable because I was trying to keep the post simple. (Brevity is not my strong suit.) Kudos to you. I've set your response as the answer. – RLH Jun 01 '11 at 20:47
  • "A note of caution with this approach would be that the next person looking into the package may not be aware why a particular task didn't execute on a given date (in your case first day of the month)" - this is the EXACT cause of my confusion this morning (finally figured it out from this post). – tbone Jun 13 '16 at 16:37