Automate the process by running an excel VBA macro in SSIS
Lately I need a project to automate the process by merging the SSIS package and extracting the VBA macro into one. Below are the steps:
-
I have an SSIS package that exports the entire view result to several separate files from sql server in order to succeed. All files are saved in the same location.
-
I have one excel VBA macro to clear all blank sheets in each exported excel file.
-
I also have an excel VBA macro doing a merge task to merge the entire excel file into a master excel file. This master excel file contains the entire result set, and each result set is saved in different tabs respectively.
Since I am manually following steps 2 and 3, so my question is how to combine step 2 and step 3 with step 1 to combine them as one automation process.
Please provide me with advice on how this is possible. Many thanks.
source to share
The way to do this is to create a script task in your SSIS package.
Then, once inside the task script, you can call the Excel interval through the C # code of the task script. for example you can add a link to Microsoft.Office.Interop. After you use this library in your C # task script code, you can add code that will call the macro. eg.
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 other workbooks, as well as any automation you need after that - for example, you can close a workbook and open another workbook, using Excel Automation as needed.
source to share