0

I have an SSIS package that runs a number of Data Flow Tasks all at the same time.

Each Data Flow Task is placed inside a Sequence Container.

Before the Data Flow Tasks are run, a Script Task is used to check what Data Flow Tasks are required to run.

Each Data Flow Task also has a related variable, which is set to true or false by the Script Task. The Data Flow Tasks use the variable value as a Disable Expression property.

When run initially, the process works as expected. Those Tasks that have variable value = true do run, and those that have a variable value = false do not run.

However, the next time I go to run the package all Data Flow Tasks are disabled initially (probably because the related variables are set to false).

But even when some variables are then set to true at runtime, the Tasks remain disabled. The Disable Expression does not seem to pick up the latest variable value. (in the Watch window I can see the variable values have updated correctly)

I tried setting 'DelayValidation' = true on each Task but no change.

Any reason why this happens?

enter image description here

1

I would add a script task at the beginning of the package, or right before the tasks in questions.

The script task should:

  • Take in the Variables (like User::IsCounterpartyReportRequired)
  • Display those variables in a message box (via MessageBox.Show(Variables.IsCounterpartyReportRequired.ToString())

This way you can see the variables at any stage of the package you wish, and find out if there is some other task that's making them change value. It's a "quickwatch" style debugging but with MessageBox.

Just make sure you don't leave the messageBox Commands running before you deploy, as your package will fail.

6
  • In the Watch window I can see the variable values have updated correctly. I set a breakpoint right before the Tasks are to run and variables look good.
    – Kevin
    Feb 22 '18 at 15:11
  • I'd still recommend a script task with a message box that evaluates your exact expression for the "Disable" attribute. Either something is changing during package runtime or your expression is being set to the wrong value. Also, is the check case sensitive? I see the "p" in "party" is lowercase but you seem to be using Pascal/Proper case.
    – SQLDevDBA
    Feb 22 '18 at 15:36
  • Tried this but same issue. I am running in Debug mode in SSDT if that makes any difference?
    – Kevin
    Feb 22 '18 at 18:02
  • @Kevin nope, that's exactly the mode i'd run it in. Can you confirm the expression evaluation as well? MessageBox.Show(IsCounterpartyReportRequired.ToString()) If it displays FALSE then this task SHOULD be disabled. If it displays TRUE then the task should be ENABLED.
    – SQLDevDBA
    Feb 22 '18 at 19:14
  • Disable Expression: @[User::IsCounterpartyReportRequired] == false
    – Kevin
    Feb 23 '18 at 10:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.