Why is the business requirement to run macros thru SSIS?? That's odd, to say the least. However, I have had stranger things dictated to me ...

If you really need this vba code, use a script component in a data flow. That uses VSA, and VBA is a very close match to it. not perfect, you'll have editing to do.

Otherwise, unless there are significant reasons not to, I would recreate the macro actions is SSIS if data tranforms and loading are part of the project. If you aren't moving data, I'd question why you "have to" use SSIS if you already have the macros.

More info, please.