Conditional Split not splitting data

Question

Conditional Split not splitting data

Angela But

Hall of Fame

Points: 3848
More actions
September 23, 2009 at 12:49 am

#135729

Hi All,
I am building a SSIS data flow task to execute some sql, pass the row count to a variable used by the row count component and then I'm using the Conditional Split component to output the result set to a flat file where @rowcountvariable >= 1.
The end goal is to only create the flatfile if rowcountvariable >= 1 but it seems to create the file regardless.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

I think an Exec SQL task that returns a row count and feeds it into a variable. Then the variable is used in the precedence with expressions to determine whether the Dataflow is run at all..

You read here on expressions in precendence:

http://msdn.microsoft.com/en-us/library/ms140153(SQL.90).aspx

CEWII

Angela But Hall of Fame Points: 3848 More actions · Answer 2

Am I missing something? How do you set expressions with precedence within a Data Flow Task?

This is part of a larger ETL process that has many Data Flow tasks. Inside my Data Flow tasks I have an OLE DB Source where I have "exec storedprocA" to bring back specified records which is connected to a Row Count. The Row Count is connected to a Conditional Split. The Conditional Split is where rowcount >= 1 is then connected to a flat file.

I only want the flat file to be created upon "where rowcount >= 1", but it gets created regardless.

Like I said I have multiple Data Flow Tasks, and the execute sql tasks is not available within them. If anyone has a better way of transforming this data I'm all ears.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

I think your problem is that by the time it knows there is no records the destination object is already instantiated and the file created. You need to keep the dataflow from running at all.. Also, you might look at delayed validation. I doubt it will help but it is a thought..

you can't set expressions and precedence in the dataflow, you need to add an EXEC SQL task BEFORE the dataflow that gets a rowcount, and then if the value is >0 then the dataflow is run, you handle that part, the connection from the EXEC SQL to the Dataflow with expressions and precendence.

The way to do the expressions and precedence is in the article I referenced last time.

CEWII

Angela But Hall of Fame Points: 3848 More actions · Answer 4

I got rid of my count and conditional split in my data tasks and just used the execute sql task in the control flow to manage the conditional split where rowcount > 1. My package looks really messy now, but I guess this is the only way to conditionally output a file.

Thanks.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

Unfortuantely I think you are right.. I can't think of another way to get it to work the way you want it to..

CEWII

Tim Mitchell SSCoach Points: 15681 More actions · Answer 6

Angela,

You are correct - the file will be created even if there are no rows in your output, and there's no way within the data flow to prevent this.

A cleaner way to do this would be to use the rowcount as before, but remove the conditional split from your data flow. Drag over a filesystem task that will delete the output file you just created. Now connect your existing data flow to the new filesystem task, and use an expression constraint to only execute the new task if the rowcount == 0.

hth,

Tim

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices