This is a simp,e description opf the steps you need to do; as I don't have a lot of detail to go on.

OK, I would firstly create a Execute SQL Statement task, and return the number of rows to a variable. In your package as well, you'll also need for few more further variables for the FileName, Offset and Fetch values, and another for your dynamic SQL.

The SQL variable will need to be something like:
="SELECT YourColumns FROM YourTable WHERE YourWhereCriteria ORDER BY YourIDColumn OFFSET " + (DT_WSTR,10) user::Offset + " ROWS FETCH NEXT " + (DT_WSTR,10) user::Fetch + " ROWS ONLY;" (this is UNTESTED code)

Create a For Loop container, and set it to loop while the value of Off Set is less than the value of your total rows variable. In there, create your dataflow, and use the value of your dynamic SQL as the source. Then export that data to your file (which'll have a dynamic name).

Then, after the dataflow, use a Expression task to increase the value of your Offset variable by your Fetch Variable's value, and then another to update the name of your filename (or have the filename as an expression, derived off the valyue of OFFSET). Your container will then loop until all the rows are processed, generating a new file each time.