通过在SSIS中运行excel VBA宏来自动化进程

问题描述

最近，我有一个项目需要通过将SSIS包和excel VBA宏组合在一起来自动化一个进程。以下是步骤：

1. 我有一个SSIS包将所有的视图结果导出到多个单独的文件从sql server到excel。所有文件都保存在相同的位置。




2. 我有一个excel VBA宏执行清理以删除每个导出的Excel文件中的所有空白表。




3. 我还有一个excel VBA宏执行合并任务，将所有excel文件合并到master excel文件中。此主excel文件包含所有结果集和各个结果集保存在不同的选项卡上。

由于我手动运行步骤2和步骤3，所以我的问题是如何连接步骤2和步骤3使用步骤1将它们组合成一个自动化过程。

请提供有关可能实现的可能性的建议！非常感谢。

解决方案

这样做的方法是在SSIS包中创建一个脚本任务。 b
$ b

然后，一旦在脚本任务中，您可以通过脚本任务的C＃代码调用Excel interop。例如您可以添加对Microsoft.Office.Interop的引用。一旦您在脚本任务的C＃代码中使用该库，您可以添加一些将调用该宏的代码。例如

  oExcel = CreateObject（Excel.Application）
 oExcel.Visible = False 
 oBooks = oExcel.Workbooks 
 oBook = oBooks.Open（Dts.Variables（filePath）。Value.ToString（））
 //你的宏在这里：
 oExcel.Run（Yourmacro） 
  

然后您可以为其他工作簿编写代码，无论您需要什么自动化，例如您可以根据需要通过Excel自动化关闭工作簿并打开另一个工作簿。

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.

解决方案

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.

这篇关于通过在SSIS中运行excel VBA宏来自动化进程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！