excel - 如何在不使用其他软件的情况下将 SSIS 导出到 Microsoft Excel？

Question

这个问题很啰嗦，因为我很长一段时间以来一直在更新这个问题，试图让 SSIS 正确导出 Excel 数据。 我设法解决了这个问题，尽管不正确。除了有人提供正确答案之外，这个问题列出的解决方案并不糟糕。

我发现的唯一答案是创建一个足以容纳我的列的单行命名范围。在命名范围中放置示例数据并隐藏它。 SSIS 追加数据并从单行读取元数据(距离足够近，可以在其中放入内容)。 数据采用隐藏单行的格式。这允许标题等。

哇，屁股真痛。需要超过 450 天的导出才能弥补损失的时间。不过，我仍然喜欢 SSIS，并将继续使用它，因为它仍然比 Filemaker 好得多，哈哈。我的下一次尝试将在报表服务器中执行相同的操作。

<小时/>

原始问题注释:

如果您在 Sql Server Integrations Services 设计器中，并且想要将数据导出到 Excel 文件，从第一行以外的其他行开始，比如说第四行，您如何指定这一点？

我尝试进入数据流的 Excel 目标，将 AccessMode 从变量更改为 OpenRowSet，然后将变量设置为“YPlatters$A4:I20000”，但失败，表示找不到工作表。该工作表称为 YPlatters。

我认为您可以指定(Sheet$)(起始单元格):(结束单元格)？

Update

Apparently in Excel you can select a set of cells and name them with the name box. This allows you to select the name instead of the sheet without the $ dollar sign. Oddly enough, whatever the range you specify, it appends the data to the next row after the range. Oddly, as you add data, it increases the named selection's row count.

另一个奇怪的事情是数据采用指定范围的最后一行的格式。我的标题行是粗体的。如果我指定以标题行结尾的范围，数据将附加到下面的行，并使所有条目变为粗体。如果指定低一行，则会在标题行和数据之间放置一个空行，但数据不是粗体。

Another update

No matter what I try, SSIS samples the "first row" of the file and sets the metadata according to what it finds. However, if you have sample data that has a value of zero but is formatted as the first row, it treats that column as text and inserts numeric values with a single quote in front ('123.34). I also tried headers that do not reflect the data types of the columns. I tried changing the metadata of the Excel destination, but it always changes it back when I run the project, then fails saying it will truncate data. If I tell it to ignore errors, it imports everything except that column.

几天或几个小时后的一篇作品......

Another update

I tried every combination. A mostly working example is to create the named range starting with the column headers. Format your column headers as you want the data to look as the data takes on this format. In my example, these exist from A4 to E4, which is my defined range. SSIS appends to the row after the defined range, so defining A4 to E68 appends the rows starting at A69. You define the Connection as having the first row contains the field names. It takes on the metadata of the header row, oddly, not the second row, and it guesses at the data type, not the formatted data type of the column, i.e., headers are text, so all my metadata is text. If your headers are bold, so is all of your data.

I even tried making a sample data row without success... I don't think anyone actually uses Excel with the default MS SSIS export.

如果您可以定义没有标题行的“插入范围”(A5 到 E5)，并设置这些列的格式(货币、非粗体等)，而不会在 Excel 中跳过一行，这将非常有帮助。据我所知，没有人使用 SSIS 在没有第三方连接管理器的情况下导出 Excel。

关于如何正确设置此设置以使数据格式正确(即，从 Excel 读取的元数据适合真实数据，并且格式继承自第一行数据，而不是 Excel 中的标题)的任何想法？

One last update (July 17, 2009)

I got this to work very well. One thing I added to Excel was the IMEX=1 in the Excel connection string: "Excel 8.0;HDR=Yes;IMEX=1". This forces Excel (I think) to look at all rows to see what kind of data is in it. Generally, this does not drop information, say for instance if you have a zip code then about 9 rows down you have a zip+4, Excel without this blanks that field entirely without error. With IMEX=1, it recognizes that Zip is actually a character field instead of numeric.

And of course, one more update (August 27, 2009)

The IMEX=1 will succeed importing data with missing contents in the first 8 rows, but it will fail exporting data where no data exists. So, have it on your import connection string, but not your export Excel connection string.

我不得不说，经过这么多的摆弄，效果非常好。

附注如果您使用的是 x64 位版本，请确保从 C:\Program Files\Microsoft SQL Server\90\DTS.x86\Binn 调用 DTExec。它将加载 32 位 Excel 驱动程序并正常工作。

Answer 1

在脚本任务中创建 Excel 工作簿，然后稍后在流程中选择它会更容易吗？

SSIS 的引擎部分很好，但与 Excel 的集成很糟糕

“将 SSIS 与 Excel 结合使用就像将热 tar 倒入路锥中的 iHole”