Post reply

If there's output

  • August 8, 2011 at 4:49 pm

    #390146

    Hi All,

    I have built a whole bunch of packages in visual studio 2005 that I will be adding to SSMS as regular jobs. The packages import data rows from SQL 2005 tables, then some data conversion and derived columns and finally exporting to a excel file.

    This all works well, but what I am wondering is if there is a control or data flow that will only copy the excel file if there were actually rows in the output? Becuase each SQL package doesn't always have results.

    I can do it with a file system task, but this moves the file everytime not if there are results\output.

    Thanks

  • August 9, 2011 at 1:52 am

    #1365654

    You can add a rowcount in your dataflow. (this stores the result in a variable)

    Then, after the dataflow, you can use the variable value in an expression on the precedence constraints in your control flow, so that you can conditionally manipulate the control flow.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • August 9, 2011 at 9:25 am

    #1366009

    I agree Koen, this is a good methodology. The only thing better would be to be able to determine that there would be nothing to do in the first place and skip the whole data-flow entirely.

    CEWII

  • August 9, 2011 at 6:56 pm

    #1366363

    Koen Verbeeck (8/9/2011)

    You can add a rowcount in your dataflow. (this stores the result in a variable)

    Then, after the dataflow, you can use the variable value in an expression on the precedence constraints in your control flow, so that you can conditionally manipulate the control flow.

    This is a good idea, but i must be missing something.

    Error when running "expression @rowcount>0 must evalute to true or false, change the expression to evaluate to a boolean value."

    Added row count to the data flow before the destination file, with variable Rowcount. Control flow, added an expression to the green line before the file system move control.

    Precedence Constraint Editor;

    Eval: Expression

    Value: Success

    Expression: @rowcount>0

    multiple Constraints: logical AND

  • August 9, 2011 at 7:50 pm

    #1366369

    Found the problem the variable had to be package wide not just data flow task in scope.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply