postgresql 如何确定 SSIS 转换中的错误行/列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12750423/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to determine faulty row/column in SSIS transformation
提问by Webjedi
I'm running a DTSX package to move data from SQL to Postgres using the PGOLEDB Native Postgres driver and I'm receiving an error (below). How do I read this error and determine what row/column is causing the issue?
我正在运行一个 DTSX 包，以使用 PGOLEDB Native Postgres 驱动程序将数据从 SQL 移动到 Postgres，但我收到一个错误（如下）。如何阅读此错误并确定导致问题的行/列？
I'm receiving the following error (PGNP-SE-1.4.3076):
我收到以下错误 (PGNP-SE-1.4.3076)：
OnError,POSTGRESDEV,DOMAIN\USERNAME,Data Flow Task 9,{1EE44D97-78DD-4175-8162-2520654A750A},{F27E0BD1-8850-4497-BD9B-BDAFA7BDA401},10/4/2012 8:46:13 AM,10/4/2012 8:46:13 AM,-1071636471,0x,SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "PostgreSQL Native Provider" Hresult: 0x80004005 Description: "ERROR: invalid byte sequence for encoding "UTF8": 0x96
CONTEXT: COPY TransHist, line 390
".
OnError,POSTGRESDEV,DOMAIN\USERNAME,full_export_new,{79EBE819-217A-4AB2-BF58-45A6D6A3B4B8},{F27E0BD1-8850-4497-BD9B-BDAFA7BDA401},10/4/2012 8:46:13 AM,10/4/2012 8:46:13 AM,-1071636471,0x,SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "PostgreSQL Native Provider" Hresult: 0x80004005 Description: "ERROR: invalid byte sequence for encoding "UTF8": 0x96
CONTEXT: COPY TransHist, line 390
".
OnError,POSTGRESDEV,DOMAIN\USERNAME,Data Flow Task 9,{1EE44D97-78DD-4175-8162-2520654A750A},{F27E0BD1-8850-4497-BD9B-BDAFA7BDA401},10/4/2012 8:46:13 AM,10/4/2012 8:46:13 AM,-1071607767,0x,SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Destination Input" (95)" failed because error code 0xC020907B occurred, and the error row disposition on "input "Destination Input" (95)" 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.
OnError,POSTGRESDEV,DOMAIN\USERNAME,full_export_new,{79EBE819-217A-4AB2-BF58-45A6D6A3B4B8},{F27E0BD1-8850-4497-BD9B-BDAFA7BDA401},10/4/2012 8:46:13 AM,10/4/2012 8:46:13 AM,-1071607767,0x,SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Destination Input" (95)" failed because error code 0xC020907B occurred, and the error row disposition on "input "Destination Input" (95)" 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.
OnError,POSTGRESDEV,DOMAIN\USERNAME,Data Flow Task 9,{1EE44D97-78DD-4175-8162-2520654A750A},{F27E0BD1-8850-4497-BD9B-BDAFA7BDA401},10/4/2012 8:46:13 AM,10/4/2012 8:46:13 AM,-1073450974,0x,SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination 40 - TransHist" (82) failed with error code 0xC0209029 while processing input "Destination Input" (95). 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.
OnError,POSTGRESDEV,DOMAIN\USERNAME,full_export_new,{79EBE819-217A-4AB2-BF58-45A6D6A3B4B8},{F27E0BD1-8850-4497-BD9B-BDAFA7BDA401},10/4/2012 8:46:13 AM,10/4/2012 8:46:13 AM,-1073450974,0x,SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination 40 - TransHist" (82) failed with error code 0xC0209029 while processing input "Destination Input" (95). 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.
UPDATE:I think the encoding error is a red-herring. When I run this it fails on row 1000 (successfully writes 999 rows). If I change the source to a view that limits the results to less than 1000 rows (say 500) it succeeds (but obviously not all the data is moved). If I change the destinations connection string to BULK_INSERT=500...it will fail on row 500 and if I change the view to less than 500 it succeeds.
更新：我认为编码错误是一个红鲱鱼。当我运行它时，它在第 1000 行失败（成功写入 999 行）。如果我将源更改为将结果限制为少于 1000 行（比如 500）的视图，它会成功（但显然并非所有数据都被移动）。如果我将目标连接字符串更改为 BULK_INSERT=500...它将在第 500 行失败，如果我将视图更改为小于 500，它会成功。
UPDATE 2:Setting BULK_INSERT to 0 fixes the issue...I'm sure that has negative effects on performance though.
更新 2：将 BULK_INSERT 设置为 0 可以解决这个问题……但我确信这会对性能产生负面影响。
回答by Bill Anton
A quick way to determine which row(s) are responsible for the error is to error behavior from the default of "fail component" to "redirect rows" and then pipe the error output to a flat file.
确定哪些行导致错误的一种快速方法是将错误行为从默认的“失败组件”改为“重定向行”，然后将错误输出通过管道传输到平面文件。
For your particular error, the key message is:
对于您的特定错误，关键信息是：
"ERROR: invalid byte sequence for encoding "UTF8": 0x96
“错误：编码“UTF8”的字节序列无效：0x96
Google'ing this string brings back a few hits indicating a string encoding issue. Here's a linkfrom the google-results back to stackoverflow.
谷歌搜索这个字符串会带回一些表示字符串编码问题的点击。 这是从 google-results 返回到 stackoverflow的链接。
You could try using a data conversion task to specify the correct encoding in the data flow task or you could make adjustments on the destination system (but I'm not really familiar with Postgres so can't help you there)
您可以尝试使用数据转换任务在数据流任务中指定正确的编码，或者您可以对目标系统进行调整（但我对 Postgres 不太熟悉，因此无法帮助您）
