You can try using the transformation between SQL server to MS Excel. If this doesn’t work
than use ActiveX script to generate the MS Excel file which will able to handle dynamic column names.
This will involve writing macro which will select the required columns from table in SQL and put the same in MS Excel file and save the same with new name each time.
This macro will be called from Active X script which will run through DTS.
If you need further help, please do write. I will let u the activeX scripts to run the macro.