SSIS Condition split based on column value

Good Day All,

I have a select query where i pull the data from sql ie

select invno , date_received from sales

What i want to do is split the file into multiple files using conditional split. I don't know how to set the condition i tried as below

But it just creates one file, how do i create the multiple files based on column value if i don't know what the column value would be?

Also i would like to assign the column value ie INVNO to filename as well to prevent overwriting of files

Using the out of of the box componentry, your best bet would be to have something like this

The Execute SQL Task will return a Full Result Set to an SSIS variable of type Object. Your query would generate the distinct set of INVNO. SELECT DISTINCT T.INVNO FROM dbo.Sales AS T;

The Foreach Loop Container is then going to "shred" that recordset into a single instance of our INVNO. This requires you to have a variable, probably of type String, to receive that number.

The Data Flow Task will have as the source query a parameterized query. Assume OLE DB Connection manager, that'd be select INVNO, date_received FROM dbo.Sales AS S WHERE S.INVNO = ?; and then you map in the current value of INVNO (assigned as part of the shredding from the FELC)

Finally, the Flat File Connection Manager will have an Expression on the ConnectionString property that factors in the full path to the output file. It could be something as simple as "C:\ssisdata\" + @[User::Invno] + ".csv"

Oh, and set DelayValidation = True for the Flat File Connection Manager and the Data Flow Task.