通过在 SSIS 中运行 excel VBA 宏来自动化流程 [英] Automate process by running excel VBA macro in SSIS

问题描述

最近，我有一个项目需要通过将 SSIS 包和 excel VBA 宏合二为一来自动化流程.以下是步骤:

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. 我有一个 SSIS 包，可将所有视图结果从 sql server 导出到多个单独的文件到 excel.所有文件都保存在同一位置.

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.

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

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

我还有一个 excel VBA 宏执行合并任务，将所有 excel 文件合并到主 excel 文件中.这个主 excel 文件包含所有结果集，每个结果集相应地保存在不同的选项卡上.

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.

由于我手动运行第 2 步和第 3 步，所以我的问题是如何将第 2 步和第 3 步与第 1 步连接起来，将它们组合为一个自动化流程.

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.

推荐答案

执行此操作的方法是在您的 SSIS 包中创建一个脚本任务.

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

然后，一旦进入脚本任务，您就可以通过脚本任务的 C# 代码调用 Excel 互操作.例如您可以添加对 Microsoft.Office.Interop 的引用.在脚本任务的 C# 代码中使用该库后，您可以添加一些将调用宏的代码.例如

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")

然后您也可以为其他工作簿编写代码，用于此后需要的任何自动化 - 例如您可以根据需要通过 Excel 自动化关闭工作簿并打开另一个工作簿.

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 宏来自动化流程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！