dynamic excel destination depend on dataflow data

I created a data flow with complaicated SQL. There is "type" field in the output column.

I would like to created excel files for each "type" value

E.g. If there is 3 "type" values (A, B, C), I would like to create 3 excel files to store type A, type B, and type C data respectively.

Since the number of possibe value of "type" field is various, how can I create the xls destination dynamic and move the correct type to the corresponding excel file?

The conditional split has fixed conditions, it is not suitable for by dynamic number of value

For Loop condition is not a good choice because I need to run the complicated SQL for many time.

Thanks.

Put all data in one place, but with the additional type column. Then drive a loop to extract each type of data into a file. This assumes that each types file has the same format, and would require two data flows, the latter of which would be inside the loop.

I would store the list of unique types in advance, perhaps by multicasting the preparatory flow just prior to writing the full file, and then getting distinct type values, use the Sort component.

Your second data flow would just be source, conditional split acting as a filter then the destination. The split filter would be based on typecolumn == @.typevariable. @.typevariable would be set from the loop enumerator, it is type value for that iteration. I would also use that variable in an expression on the destination connection to give a type specific filename.