Automate process by running excel VBA macro in SSIS

Question

Recently, I have a project need to automate a process by combining SSIS package and excel VBA macro into one. Below are the steps:

1. I have a SSIS package exporting all the view result to multiple individual file from sql server to excel. All the files are saved in same location.

2. I have one excel VBA macro perform cleaning to remove all the empty sheets in each exported excel file.

3. I also have a excel VBA macro perform merging task to merge all the excel file into in master excel file. This master excel file contains all the result set and each result set saved on different tabs accordingly.

Since I am manually running step 2 and step 3, so my question is how should connect step 2 and step 3 with step 1 to combine them as one automate process.

Please provide me advice on how likely this can be achieved! Thanks a lot.

Solution

The way to do this is to create a script task in your SSIS package.

Then, once inside the script task, you can call the Excel interop through the C# code of the script task. e.g. you can add a reference to Microsoft.Office.Interop. Once you are using that library in your C# code of the script task, you can add some code that will call the macro. e.g.

oExcel = CreateObject("Excel.Application")
oExcel.Visible = False
oBooks = oExcel.Workbooks
oBook = oBooks.Open(Dts.Variables("filePath").Value.ToString())
//Your macro here:
oExcel.Run("Yourmacro")

Then you could write code for the other workbooks aswell, for whatever automation you need after this - e.g. you can close the workbook and open another workbook, through the Excel automation as needed.

Comments

[TDai]

Thanks Robert, this is very helpful!