在SSDT中创建SSIS包。数据流任务中的条件拆分导致打包包 [英] Creating SSIS Package in SSDT. Conditional Split in Data Flow Task Causes Package To Break

问题描述

我正在尝试将表从SQL SERVER 2012导出到Excel 2007 Workbook。

I'm trying to export a table from SQL SERVER 2012 to An Excel 2007 Workbook.

来自OLE DB Source - > Excel Destination的简单任务完美。

The simple task from OLE DB Source -> Excel Destination works perfectly.

我有一个名为[POD Assignment]的字段，如果[POD Assignment]等于UNKNOWN，我想导出到工作簿中的一个工作表，另一个

I have a field called [POD Assignment], I want to export to one sheet in the workbook if [POD Assignment] is equal to "UNKNOWN", and to another sheet if it's not.

将OLE DB来源链接到条件拆分

Link OLE DB Source to Conditional Split.

我有一个名为Unknown的输出条件是[POD Assignment] ==UNKNOWN。
我将输出未知链接到Excel目标。

I have an output named "Unknowns" where the condition is "[POD Assignment] == "UNKNOWN"". I link the the output "Unknowns" to an Excel Destination.

在这一点上，当我运行包时，一切都应该是正常的。行正确导出到工作簿，计数是正确的。如下所示：

At this point, when I run the package, everything works as it should. The rows were exported correctly to the workbook, and the count was right. As seen here:

[] http：// imgur.com/iKcJCfY,rHnW8ax#0

现在我将条件拆分连接到第二个excel目标，其中输出是条件拆分的默认输出。 excel第二个目的地使用与第一个相同的确切Excel连接。并且所有的数据应该导出到同一个工作簿中的另一个表。

Now I connect the conditional split to a second excel destination where the output is the Default Output of the conditional split. The excel second destination uses the same exact Excel Connection as the first. And all the data is supposed to exported to a different sheet in the same workbook.

运行失败。如下所示：

[]： http：/ /imgur.com/iKcJCfY,rHnW8ax#1

任务过早地中止了错误。

The task prematurely aborts on error.

我收到以下错误代码：

[Excel目的地1 [101 ]]错误：SSIS错误代码DTS_E_OLEDBERROR。发生OLE DB错误。错误代码：0x80004005。
可以使用OLE DB记录。资料来源：Microsoft Office Access数据库引擎Hresult：0x80004005说明：无法扩展命名范围。

[Excel Destination 1 [101]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft Office Access Database Engine" Hresult: 0x80004005 Description: "Cannot expand named range.".

[Excel目标1 [101]]错误：SSIS错误代码DTS_E_INDUCEDTRANSFORMFAILUREONERROR。 Excel目标1.输入[Excel目的地输入]失败，因为出现错误代码0xC020907B，并且Excel目标1.输入[Excel目标输入]上的错误行排列指定错误失败。指定组件的指定对象发生错误。在此之前可能会出现错误消息，并提供有关失败的更多信息。

[Excel Destination 1 [101]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Excel Destination 1.Inputs[Excel Destination Input]" failed because error code 0xC020907B occurred, and the error row disposition on "Excel Destination 1.Inputs[Excel Destination Input]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

[SSIS.Pipeline]错误：SSIS错误代码DTS_E_PROCESSINPUTFAILED。处理输入Excel目标输入（112）时，组件Excel目标1（101）上的ProcessInput方法失败，错误代码为0xC0209029。已识别的组件从ProcessInput方法返回错误。该错误特定于组件，但错误是致命的，并将导致数据流任务停止运行。在此之前可能会出现错误消息，并提供有关故障的更多信息。
[OLE DB Source [188]]错误：设置缓冲区的行集结束失败，错误代码为0xC0047020。
[SSIS.Pipeline]错误：SSIS错误代码DTS_E_PRIMEOUTPUTFAILED。 OLE DB Source上的PrimeOutput方法返回错误代码0xC0209017。当管道引擎称为PrimeOutput（）时，组件返回故障代码。故障代码的含义由组件定义，但错误是致命的，并且管道停止执行。在此之前可能会出现错误消息，有关失败的更多信息。

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Excel Destination 1" (101) failed with error code 0xC0209029 while processing input "Excel Destination Input" (112). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. [OLE DB Source [188]] Error: Setting the end of rowset for the buffer failed with error code 0xC0047020. [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on OLE DB Source returned error code 0xC0209017. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

任何帮助将不胜感激。这让我疯狂了我不明白为什么使用一个目的地的条件拆分工作完美，但是当我添加第二个目的地时，一切都会失败。

Any help would be appreciated. This is driving me crazy. I don't understand why using one destination for the conditional split works perfectly but when I add a second destination everything fails.

推荐答案

我有这个确切的错误信息。我尝试了其他答案的建议，没有工作。

I had this exact error message. I tried the suggestions on the other answers and none worked.

我的问题原来是我正在尝试同时写入不同的标签。

My problem turned out to be that I was trying to write to different tabs at the same time.

而不是写入同一个数据流任务中的每个标签，我把每个标签放入自己的数据流中，以便每次写入都会一次发生一次。这解决了问题。

Instead of writing to every tab in the same dataflow task I put each tab into it's own dataflow so that each write would happen one at a time. This fixed the problem.

这篇关于在SSDT中创建SSIS包。数据流任务中的条件拆分导致打包包的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！