SSIS 隐藏工作表作为 Excel 目标 [英] SSIS hidden sheets as Excel Destination

问题描述

因为在 SSIS (v 2008) 中，一些简单的事情经常发生，但并不那么简单

As occurring more often something simple is not so simple in SSIS (v 2008)

情况之前 B.I.进来:数据被加载到隐藏的Excel表格中的链接表中，有宏和手动调整.

Situation before B.I. came in: data was loaded into linked tables in hidden excel sheets, there were macro's and manual adaptations.

之后:仍然是隐藏的工作表，但我们用 SSIS 替换了宏功能，并且不得不取消表格的链接以消除打开文件时的弹出窗口:以下数据可能已丢失:-表"

After: Still hidden sheets but we replaced macro functionalities with SSIS and had to unlink the table to get rid of the pop-up when opening the files: "The following data may have been lost: -Table"

由于我们取消了表格的链接(并因此删除了连接)，我们无法再通过 SSIS:Excel 目标处理隐藏的工作表.非隐藏的工作表没有问题并且是可见的.

Since we unlinked the table (and by consequence deleted the connection) we can't address the hidden sheet anymore via SSIS: Excel Destination. Non-hidden sheets are no problem and are visible.

--> 问题:如何导出到隐藏的excel表?由于上述操作，该工作表在 Excel 目标窗口中不再可见，请使用下拉框Excel 工作表的名称".

--> Question: how can you export to a hidden excel sheet? Since above action the sheet is not visible anymore in the Excel Destination windows, dropbox "name of the excel sheet".

提前感谢您的建议！！

推荐答案

当 GUI 不允许您在 SSIS 中执行某些操作时，诀窍是告诉它您知道自己在做什么.;)

The trick when the GUI doesn't let you do something in SSIS is to tell it you know what you are doing. ;)

在这种情况下，下拉菜单不允许您从隐藏选项卡中选择值.连接管理器指向一个 Excel 文件，它有 2 个选项卡，Sheet1 和隐藏的隐藏.

In this case, the drop down does not allow you to select values from a hidden tab. The connection manager points to a Excel file with 2 tabs, Sheet1 and Hidden which is hidden.

然而，它允许您选择来自变量的表名或视图名"或SQL 命令".此解决方案将使用变量方法，但命令应该也能正常工作.

It does however allow you to select "Table name or view name from variable" or "SQL Command." This solution will use the variable approach but command should work just as well.

如您所见，我定义了一个名为 SheetName 的变量并为其分配了一个值 Hidden$

As you can see, I have defined a variables called SheetName and assigned it a value of Hidden$

示例数据流

结果

Progress: 2011-11-07 07:49:32.80
   Source: Excel Destination
   Cleanup: 100% complete
End Progress
DTExec: The package execution returned DTSER_SUCCESS (0).
Started:  7:49:30 AM
Finished: 7:49:32 AM
Elapsed:  2.121 seconds

这篇关于SSIS 隐藏工作表作为 Excel 目标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！