SSIS错误-值违反了列的架构约束 [英] SSIS error - value violated the schema's constraint for the column

问题描述

使用ssis从sql服务器导出到excel.通过写入excel文件，导出大约占80％，然后失败，并显示此错误.每次都在同一行停止.所写字段中的值位于length参数中，并且不包含任何时髦字符.因为35行成功通过并写入excel文件，所以我认为数据类型是正确的(在SQL Server中为nvarchar(max)，在SSIS中为DT_WSTR(4000)).还有什么其他事情可以触发此错误?使用SSIS 2008，Excel 2007输出文件.

Exporting from sql server to excel with ssis. export gets around 80% through writing to the excel file then fails with this error. It stops at the same row each time. The value in the field being written is the well within the length parameter and contains no funky characters. Because 35 rows pass through successfully and write to the excel file, I assume the data type is correct (nvarchar(max) in sql server, DT_WSTR(4000) in SSIS). What other things can trigger this error? Using SSIS 2008, excel 2007 output file.

错误消息:

错误:数据流任务1，Excel目标[1380]处错误0xC0202009:SSIS错误代码DTS_E_OLEDBERROR.发生OLE DB错误.错误 代码:0x00040EDA.在数据流任务1，Excel时错误:0xC020901C 目标[1380]:输入栏化学药品"存在错误 输入"Excel Destination Input"(1391)时输入(1454).列状态 返回的是:该值违反了架构的约束 列."

Error: 0xC0202009 at Data Flow Task 1, Excel Destination [1380]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x00040EDA. Error: 0xC020901C at Data Flow Task 1, Excel Destination [1380]: There was an error with input column "Chemicals" (1454) on input "Excel Destination Input" (1391). The column status returned was: "The value violated the schema's constraint for the column.".

错误:数据流任务1，Excel目标[1380]处为0xC0209029: SSIS错误代码DTS_E_INDUCEDTRANSFORMFAILUREONERROR. 输入 "Excel目标输入"(1391)"失败，因为错误代码为0xC0209076 发生，并且错误行配置在输入" Excel Destination上 输入(1391)"指定错误失败.发生错误 指定组件的指定对象.可能有错误 在此之前发布的消息，其中包含有关失败的更多信息.

Error: 0xC0209029 at Data Flow Task 1, Excel Destination [1380]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Excel Destination Input" (1391)" failed because error code 0xC0209076 occurred, and the error row disposition on "input "Excel Destination Input" (1391)" 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.

错误:数据流任务1(SSIS)出现0xC0047022.管道:SSIS错误 代码DTS_E_PROCESSINPUTFAILED.组件上的ProcessInput方法 "Excel目标"(1380)失败，错误代码为0xC0209029 处理输入"Excel目标输入"(1391).确定的 组件从ProcessInput方法返回错误.错误是 特定于组件，但错误是致命的，将导致 数据流任务停止运行.可能发布了错误消息 在此之前，有关失败的更多信息.

Error: 0xC0047022 at Data Flow Task 1, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Excel Destination" (1380) failed with error code 0xC0209029 while processing input "Excel Destination Input" (1391). 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.

推荐答案

我相信单个Excel列的限制实际上是255个字符.我将测试SSIS流中上游的较长值，然后拆分这些行或使用派生列"截断长数据.

I beleive the limit for a single Excel column is actually 255 characters. I would test for longer values upstream in the SSIS flow and either split out those rows or truncate long data using a Derived Column.

这篇关于SSIS错误-值违反了列的架构约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！