Q:

SSIS: How to programmatically add columns on Excel file before processing it on data flow task

I would like to request your expertise on this one, any response would be greatly appreciated. Here's my problem:
I have 10 versions of the Excel Macro File. I would need to import the data from excel files to MS SQL server database using SSIS, having OLEDB as my Data Source Connection for Excel Macro files.
The most recent version of Excel Macro (10th), is the one i am using as my source (and metadata base for columns) on Data Flow task. The old version of the files, does not contain the same amount of columns that the latest version has. If I executed my packages using the old version of the excel file as my source, it will throw an error because the columns are out of sync.
Since I have hundreds of Excel Macro files per version and theres no proper naming convention, I thought what I could do is:

Use For Each Loop - to loop through all the macro files 
Validate the excel file via Scripting Task, check the missing
columns. If there are missing columns, modify the file, add the
columns then save it. 
Execute the data flow task

I'm tried searching about this but I could not find any detailed article on this one. I heard CozyRoc has this capability, but their components are pretty expensive. I could create 10 versions of the packages but, i find this tedious, and as much as possible I don't want to result into this.
Any solutions to this one or is there any other alternative to this?
Sorry for the bad english. Thank you very much!

A:

Hmmm, i know this was posted a year a go.. But just in case i can still help mostly for those who might come across with this same question and try to seek some answers.
Well lately i've been having troubles with sp_makewebtask which requires me to search about SSIS approach:
Then i found this link here:
how to create dynamic excel sheets based on table data
For me what you will find on that link will give you an idea to programmatic-ally create add columns on Excel file before processing it on data flow task.

