Running a macro from SSIS

1. Make a "Scrip Task" from the SSIS Tool BOx in Control Flow
2. Edit Script
3. Copy paste this:

public void Main()

{

// TODO: Add your code here

            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

            Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlApp.Workbooks.Open("C:\\Users\\jla115\\Desktop\\Book2.xlsm"); // absolute path needed

            xlApp.Run("myMacroInModule"); // method overloads allow you to send it parameters, etc.

          

            xlWorkBook.Close(true); // first parameter is SaveChanges

            xlApp.Quit();

Dts.TaskResult = (int)ScriptResults.Success;

}

Note: You need to make a Reference to the Microsoft Excel 14.0 Object Library

do that from the Project Tab --> Add Reference