The only way to do this (and it's not a good way) is to create an expression that populates a T-SQL task that creates the Excel spreadsheet on the fly.

The T-SQL would look something like this:

CREATE TABLE `MyReport`(

`MyId` LongText,

`Name` LongText,

`BeginDate` LongText,

`EndDate` LongText,

`OrderNumber` LongText)

GO

I don't have any samples on my current stuff that includes mapping to integars, etc., but I image you'd use Number instead of LongText.

As far as how to make the connection manager dynamic, you'd use an Excel connection manager with an expression for the file location and name. Make sure to turn Delay Validation to True on all of these.