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)

在之前的情况进入：数据被加载到隐藏的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替换宏功能，不得不取消链接表，以便在打开文件时摆脱弹出窗口：以下数据可能已丢失：-Table

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

提前感谢任何建议！ ！
L

Thanks in advance for any advice!! L

推荐答案

当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. ;)

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

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的变量，并赋予它一个值H $

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屋！