January 26, 2010 at 8:05 pm
Here is the problem, I have a Conditional Split with multiple outputs based on certain conditions. Lets say that one of the conditions is not reached, therefore no rows are sent thru that branch. Now following that branch, I have a Script Component task that sets a package variable, but I do not want it to be changed if there are no rows that came thru. So my question if how can I not have that script component execute? Because that is the problem, the script component is executing even if no rows came thru that path... ???
Thanks in advance.
Ray
January 27, 2010 at 12:54 am
Add a Row Count Transformation between your Conditional Split and your Script. In the Script, check the value of the row count: if it is 0, leave your variable unchanged, otherwise set it to the new value.
Assuming that the row count is stored in the variable CNT, you would use something like the following code (example in C#) in the PostExecute part of your script:
Variables.MyVariable = Variables.CNT == 0 ? Variables.MyVariable : 100;
where you replace 100 with the value you want to assign.
January 27, 2010 at 12:58 am
*One* way (and I am sure there are many others) to achieve this would be to:
- create a variable to store/hold row count
- add a Row Count transformation after the Split transform (row count doesn't update the variable until the last row is passed through, hence the need for a separate DF as noted below)
- you'll need a separate data flow, in this data flow, or in the control flow that contains it
- set the constraint between the rowcount and script to be 'Success' and a conditional expression, where the expression will be something like @[User::myRowCount] > 0
HTH,
Steve.
January 27, 2010 at 6:26 am
Thanks for the posts guys! Sounds good, but the question I would really like to know is why the Script Component needs to execute if there are<nt any rows that come thru?
January 27, 2010 at 7:22 am
Because the precedence constraint is (most likely) success based on the split. This success has nothihng to do with rows passed, simply did the transform complete successfully, which is obviously, 'yes'.
Steve.
January 27, 2010 at 7:30 am
There are no precedence constraints in a Data Flow Task. I guess this is either a bug or by design. It would indeed be more convenient in some situations for tasks not to execute when there are no rows. One such situation is when you redirect e.g. errors to a file: the file will always be created, even when it is empty. So if you only want to end up with a file when there are erros you will have to delete it afterwards in your control flow in case it is empty. Very similar to the situation you are in 🙂
January 27, 2010 at 8:36 am
True, my bad (teach me to answer without looking again at a package :hehe: ). Definitely by design. Would seem to get the result the OP wanted (assuming the script task wasn't meant to actually alter the rows) that you'd want the RowCount transform and then a control flow task to execute the script based on Data Flow success plus count > 0. For the instance where you don't want a file, it's almost like you'd have to do the same thing, but you'd add a delete of the file based n the row count being 0 - a bit cludgey :S
Steve.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply