Post reply

Cond split- File containing header info produced even when conditions not met

  • May 30, 2012 at 9:32 am

    #269644

    I'm writing (or trying to anyway) a file validation process in SSIS. The outline of the data flow is shown below:

    Quick overview of the above:

    1) Flat File introduced to SSIS.

    2) Conditional split checks each row to see that it contains data that is mandatory to our company.

    3) Good data produces a flat file for pick up for futher processing.

    4) Bad data produces a flat file in a different directory to be sent back to the supplier of the original file.

    The problem I am having is that if there is no "bad data" contained with in the file being processed then SSIS is still producing an output file that contains just the header row. Is there anyway to stop this from happening?

    On a slightly unrelated note, having been thrown in at the deep end with regards to SSIS work by my company I have found this site and its members knowledge priceless. 🙂

  • May 30, 2012 at 1:38 pm

    #1494866

    I don't think there is a way to do this, but I don't know for sure. You could use a rowcount after the conditional split and if it is still 0 you could delete the file that was created.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • May 30, 2012 at 3:04 pm

    #1494923

    I could not figure a way to do this natively either. Here is a convoluted option to add to your choices. You could output the bad data output from your Split to a Recordset Destination. Then, after your Data Flow Task completes have another Data Flow Task with a precedence constraint that uses an expression to check your bad data Row Count > 0. In the second Data Flow use a Source Script Component to push the Recordset Rows to your Flat File Destination so you only write the file if there is data.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • May 31, 2012 at 2:42 am

    #1495054

    Thank you for the replies they are much appreciated, I'll give one of the work arounds suggested a go.

    Edit : Added a delete file task at the control flow level that is only called to when the reject row count is 0, this seems to work. Thank you both again for pointing me in the right direction.

  • May 31, 2012 at 4:13 am

    #1495090

    I agree with the above views that this won't be possible inside a single Data Flow Task , because you cannot set expressions on precedence constraint inside a DFT.

    This is what you can do :

    1) Create 2 DFT's. One for loading Good Data file & the other to load Bad Data file.

    2) Create a new variable (say BadCount) of int type.

    This would store the number of rows that qualify as bad data.

    (Make sure that the scope of this variable should cover both DFT's)

    3) Configure 1st DFT (to load Good data file) :

    In the first DFT,place a flat file source to read your source file.

    Below this place a Conditional Split to direct records on the basis of your test condition.

    Now place a Flat file destination that will load the Good Data file & direct the Good Data records coming from the Conditional split to this FF destination.

    Place a rowcount task & direct the Bad Data records into the rowcount task which will use the variable we defined in step-1 (Bad Count) to count the number of records.

    Don't do anything beyond this in this DFT.

    4) Configure the precedence constraint :

    Place the 2nd DFT right under the 1st DFT .

    Connect the two DFT's with a precedence constraint.

    Edit this precedence constraint & select "Expression" as the "Evaluation operation".

    Write this as the "Expression" : @BadCount>0 (replace Badcount with whatever variable you have chosen)

    5) Configure 2nd DFT (to load Bad Data)

    Place a flat file source to read your source file.

    Below this place a Conditional Split to direct records on the basis of your test condition.

    Now place a Flat file destination that will load the Bad Data file & direct the Bad Data records coming from the Conditional split to this FF destination.

    This approach will first load the Good Data file in the first Data Flow Task & at the same time count the Number of Bad data rows.

    Now if count is more that zero , the second DFT to load Bad data file will execute.

    If count is not more than zero, the second DFT will not execute at all, this will ensure that no Bad Data file is created in this case.

    Hope this helps.

    Revert back if anything is unclear.

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

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