Sometimes, you need to push back to whomever has requested "exporting all the data to Excel" as it's just not an option. Is an analyst really going to be able to do anything with a a spreadsheet with multi-million rows? No, no they will not.

Purely as an exercise of "how could I do this really bad thing"

(excluded from this exercise is a custom Script Destination as I didn't feel like writing code)

You must determine ahead of time what is a reasonable upper bound on the the number of sheets to be created. You can either limit your worksheet to a million or hit the actual limit of 1,048,576 rows per sheet. Possibly 1,048,575 rows because you want to repeat your header across sheets.

Whatever that maximum number of sheets is, N, you will need to create to create N Excel Destinations

You'll need to have a ROW_NUMBER() function applied to your source data so you'll have to have a custom query there

SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum FROM dbo.MyTable;

The Conditional Split will use the modulo function to assign rows to their paths

1. RowNum % N == 1
2. RowNum % N == 2
3. ...
4. RowNum % N == (N-1)

and the default output path is for == 0