Post reply

Do not create Flat file destination when No Rows in ADO Net Source

  • July 8, 2011 at 3:10 pm

    #244119

    Hi

    I have created a simple SSIS data transfer task which uses a ADO Net source and a Flat File destination which works fine. However when there are no rows in the ADO Net source is there a way of suppressing the creation of the Flat File.

    I have tried using a Row Counter and a Conditional Splitter with two outputs one where the Row Counter variable = 0 and one where its greater than zero and then connecting the data flow path for the greater than to the Flat File Destination. However even when the row count returns zero rows the Flat file is still created.

    Can anybody help ???

    Am I looking at this from the wrong angle as I am relatively new to SSIS.

  • July 11, 2011 at 12:53 am

    #1350896

    As you have noticed, the flat file will be generated even when there are no records written to it.

    So you'll need to check the rowcount before the dataflow and based on the result decide if the dataflow task should run or not.

    You can use a script task to determine the rowcount and to store the result in a variable. Connect the script task to your dataflow and set the precedence constraint to Success and Expression. Set the expression to:

    @RowCount > 0

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

  • July 11, 2011 at 12:04 pm

    #1351259

    Thanks for that, simple when you know how 🙂

  • July 11, 2011 at 2:13 pm

    #1351319

    david-1008051 (7/11/2011)

    Thanks for that, simple when you know how 🙂

    No problem, glad to help! 🙂

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

  • July 14, 2011 at 8:14 am

    #1352847

    I would recommend against checking the RowCount property of ADO recordset. This might be very expensive operation if you are running against large recordset because the whole recordset have to be loaded before the RowCount can be determined. What I would recommend is to check BOF and EOF property. This is very quick operation.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

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

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