I have an SSIS package thats unzips and loads a text file. It has been working great from the debugger, and from the various servers its been uploaded to on its way to our production environment.
我有一个解压缩并加载文本文件的SSIS包。它从调试器以及上传到生产环境的各种服务器上运行良好。
My problem right now is this: A file was being loaded, everything was going great, but all of the sudden, on the very last data row (according to the error message) the last field was truncated. I assumed the file we receive was probably messed up, cracked it open, and everything is good there....
我现在的问题是:正在加载文件,一切都很好,但突然之间,在最后一个数据行(根据错误消息),最后一个字段被截断。我假设我们收到的文件可能搞砸了,打开它,一切都很好....
Its a | delimited file, no text qualifier, and {CR}{LF} as the row delimiter. Since the field with the truncation error is the last field in the row (and in this case the last field of the entire file), its delimiter is {CR}{LF} as opposed to |.
它是一个|分隔文件,没有文本限定符,{CR} {LF}作为行分隔符。由于具有截断错误的字段是行中的最后一个字段(在本例中是整个文件的最后一个字段),因此其分隔符为{CR} {LF}而不是|。
The file looks pristine and I've even loaded it into Excel with no issue and no complaints. I have run this file through my local machine running the package via the deugger in VS 2008, and it ran perfectly. Has anybody had any issues with behavior like this at all? I can't test it much in the environment that its crashing in, because it is our production environment and these are peak hours.... so any advice is GREATLY appreciated.
该文件看起来质朴,我甚至将它加载到Excel中没有任何问题,也没有抱怨。我通过VS 2008中的deugger运行包的本地机器运行了这个文件,它运行得很好。有没有人对这样的行为有任何问题?我无法在它崩溃的环境中测试它,因为它是我们的生产环境,而且这些都是高峰时段....所以任何建议都非常感激。
Error message:
Description: Data conversion failed. The data conversion for column "ACD_Flag" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". End Error Error: 2013-02-01 01:32:06.32 Code: 0xC020902A Source: Load ACD file into Table HDS Flat File 1 [9] Description: The "output column "ACD_Flag" (1040)" failed because truncation occurred, and the truncation row disposition on "output column "ACD_Flag" (1040)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. End Error Error: 2013-02-01 01:32:06.32 Code: 0xC0202092 Source: Load ACD file into Table [9] Description: An error occurred while processing file "MY FLAT FILE" on data row 737541.
说明:数据转换失败。 “ACD_Flag”列的数据转换返回状态值4,状态文本“文本被截断,或者目标代码页中的一个或多个字符不匹配”。结束错误错误:2013-02-01 01:32:06.32代码:0xC020902A源:将ACD文件加载到表HDS Flat File 1 [9]描述:“输出列”ACD_Flag“(1040)”失败,因为发生了截断, “输出列”ACD_Flag“(1040)”上的截断行处置指定截断失败。指定组件的指定对象上发生截断错误。结束错误错误:2013-02-01 01:32:06.32代码:0xC0202092源:将ACD文件加载到表[9]中描述:在数据行737541上处理文件“MY FLAT FILE”时发生错误。
737541 is the last row in the file.
737541是文件中的最后一行。
Update: originally I had the row delimiter {CR}, but I have updated that to {CR}{LF} to attempt to fix this issue... although to no avail.
更新:最初我有行分隔符{CR},但我已将其更新为{CR} {LF}以尝试解决此问题...虽然无济于事。
5 个解决方案
#1
8
Update:
I am able to recreate the error message that you have added to your question. The error happens when you have more column delimiters in the line than what you have defined in the flat file connection manager.
我能够重新创建您添加到问题中的错误消息。如果行中的列分隔符多于您在平面文件连接管理器中定义的列分隔符,则会发生错误。
Here is a simple example to illustrate it. I created a simple file as shown below.
这是一个简单的例子来说明它。我创建了一个简单的文件,如下所示。
I created a package and configured the flat file connection manager with below shown settings.
我创建了一个包,并使用下面显示的设置配置了平面文件连接管理器。
I configured the package with a data flow task to read the file and populate the data to a database table. When I executed the package, it failed.
我使用数据流任务配置了包以读取文件并将数据填充到数据库表中。当我执行包时,它失败了。
Clicked the Execution Results tab on the BIDS. It displays the same message that you have posted in your question.
单击BIDS上的“执行结果”选项卡。它显示您在问题中发布的相同消息。
[Flat File Source [44]] Error: Data conversion failed. The data conversion for column "Column 1" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
[Flat File Source [44]] Error: The "output column "Column 1" (128)" failed because truncation occurred, and the truncation row disposition on "output column "Column 1" (128)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
[Flat File Source [44]] Error: An error occurred while processing file "C:\temp\FlatFile.txt" on data row 2.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Flat File Source" (44) returned error code 0xC0202092. 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.
Hope it helps to identify your problem.
希望有助于识别您的问题。
Previous answer:
I think that the value in the last field on the last row of your file probably exceeded the value of OutputColumnWidth property of the last column on the Flat File Connection Manager.
我认为文件最后一行的最后一个字段中的值可能超过了Flat File Connection Manager上最后一列的OutputColumnWidth属性值。
Right-click the Flat File Connection Manager on your SSIS package. Click Advanced tab page on the Flat File Connection Manager Editor. Click on the last column and check the value on the OutputColumnWidth property.
右键单击SSIS包上的Flat File Connection Manager。单击平面文件连接管理器编辑器上的高级选项卡页面。单击最后一列并检查OutputColumnWidth属性上的值。
Now, verify the length of data on the last field of the last row in the file that is causing your package to fail.
现在,验证导致程序包失败的文件中最后一行的最后一个字段的数据长度。
If that is cause of the problem, here are two possible options to fix this:
如果这是问题的原因,这里有两个可能的选项来解决这个问题:
-
Increase the
OutputColumnWidthproperty on the last column to an appropriate length that meets your requirements.将最后一列的OutputColumnWidth属性增加到符合要求的适当长度。
-
If you do not care about truncation warnings, you can change the truncation error output on the last column of the Flat File Source Editor. Double-click the
Flat File Source Editor, clickError Output. Change the Truncation column value to eitherIgnore failureorRedirect row. I preferRedirect rowbecause it gives the ability to track data issues in the incoming file by redirecting the invalid to a separate table and take necessary actions to fix the data.如果您不关心截断警告,则可以更改平面文件源编辑器的最后一列上的截断错误输出。双击“平面文件源编辑器”,单击“错误输出”。将“截断”列值更改为“忽略失败”或“重定向”行。我更喜欢重定向行,因为它通过将无效重定向到单独的表并采取必要的操作来修复数据,从而能够跟踪传入文件中的数据问题。
Hope that gives you an idea to resolve your problem.
希望能帮助您解决问题。
#2
4
So I've come up with an answer. The other answers are extremely well thought out and good, but I solved this using a slightly different technique.
所以我想出了一个答案。其他答案都经过了深思熟虑和良好的解决,但我用一种略有不同的技术解决了这个问题。
I had all but eliminated the actual possibility of truncation because once I looked into the data in the flat file, it just didn't make sense... truncation could definitely NOT be occuring. So I decided to focus the second half of the error message: or one or more characters had no match in the target code page
我几乎已经消除了截断的实际可能性,因为一旦我查看平面文件中的数据,它就没有意义了......截断绝对不会发生。所以我决定关注错误消息的后半部分:或者一个或多个字符在目标代码页中没有匹配
After some intense Googleing I found a few sites like this one: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/6d4eb033-2c45-47e4-9e29-f20214122dd3/
经过一番激烈的谷歌搜索后,我发现了一些像这样的网站:http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/6d4eb033-2c45-47e4-9e29-f20214122dd3/
Basically the idea is that if you know truncation isn't happening, you have characters without a code page match, so a switch from 1252 ANSI Latin I to 65001 UTF-8 should make a difference.
基本上这个想法是,如果你知道截断没有发生,你有没有代码页匹配的字符,所以从1252 ANSI Latin I到65001 UTF-8的转换应该有所不同。
Since this has been moved to production, and the production environment is the only environment having this issue I wanted to make 100% sure I had the correct fix in, so I made one more change. I had no text qualifier, but SSIS still keeps the default Text_Qualified property for each column in the Flat File Connection Manager to TRUE. I set ALL of them to false (not just the column in question). So now the package doesn't see it needs a qualifier, then go to the qualifier and see <none> and then not look for a qualifier... it just flat out doesn't use a qualifier period.
由于这已经转移到生产,并且生产环境是唯一有这个问题的环境,我想100%确定我有正确的修复,所以我做了一个更改。我没有文本限定符,但SSIS仍将Flat File Connection Manager中每列的默认Text_Qualified属性保持为TRUE。我将所有这些设置为false(不仅仅是相关列)。所以现在包看不到它需要一个限定符,然后转到限定符并查看
Between these two changes the package finally ran successfully. Since both changes were done in the same release, and I've only received this error in production and I can't afford to switch different things back and forth for experimental purposes, I can't speak to which change finally did it, but I can tell you those were the only two changes I made.
在这两个更改之间,包最终成功运行。由于这两个更改都是在同一个版本中完成的,而且我只是在生产中收到了这个错误而我无法为实验目的来回切换不同的东西,我不能说最后做了哪个更改,但是我可以告诉你,这是我做的唯一两个变化。
One thing to note: the production machine running this package is: 10.50.1617 and my machine I am developing on (and most of the machines I am testing on) are: 10.50.4000. I've raised this as a possible issue with our Ops DBA and hopefully we'll get everything consistent.
有一点需要注意:运行此软件包的生产机器是:10.50.1617,我正在开发的机器(我正在测试的大多数机器)是:10.50.4000。我已经提出这个问题作为我们的Ops DBA可能存在的问题,并希望我们能够使所有内容保持一致。
Hopefully this will help anybody else who has a similar issue. If anybody would like additional information or details (I feel as if I've covered everything) please just comment here and let me know. I will gladly update this to make it more helpfull for anybody coming along in the future.
希望这将有助于其他有类似问题的人。如果有人想要更多的信息或细节(我觉得我已经涵盖了所有内容),请在这里发表评论告诉我。我很乐意对此进行更新,以使其对将来出现的任何人都更有帮助。
#3
2
It only happens on the one server? And you aren't using a test qualifier? We have had this happen before. This is what fixed it.
它只发生在一台服务器上?你没有使用测试限定符?我们之前已经发生过这种情况。这就是修复它的原因。
Go to that server and open the xml file. Search forTextQualifier and see if it says:
转到该服务器并打开xml文件。搜索TextQualifier并查看是否显示:
<DTS:Property DTS:Name="TextQualifier" xml:space="preserve"><none></DTS:Property>
If it doesn't make it say that.
如果没有说出来的话。
#4
0
I know this is a whole year later, but when I opened the flat file connection manager, for the text qualifier it had "_x003C_none_x003E_". I replace the "_x003C_none_x003E_" hex code garbage and put arrows like it should be, "<" none ">" (the editor is removing the arrows), and it stopped dropping the last row of the file.
我知道这是一整年后,但是当我打开平面文件连接管理器时,对于文本限定符,它有“_x003C_none_x003E_”。我替换“_x003C_none_x003E_”十六进制代码垃圾并放置它应该是的箭头,“<”none“>”(编辑器正在删除箭头),它停止丢弃文件的最后一行。
#5
0
I had the exact same error. My source text file contained unicode characters and I solved it by saving the text file using unicode encoding (instead of the default utf-8 encoding) and checking the Unicode checkbox in the Data Source dialog.
我有完全相同的错误。我的源文本文件包含unicode字符,我通过使用unicode编码保存文本文件(而不是默认的utf-8编码)并检查数据源对话框中的Unicode复选框来解决它。
#1
8
Update:
I am able to recreate the error message that you have added to your question. The error happens when you have more column delimiters in the line than what you have defined in the flat file connection manager.
我能够重新创建您添加到问题中的错误消息。如果行中的列分隔符多于您在平面文件连接管理器中定义的列分隔符,则会发生错误。
Here is a simple example to illustrate it. I created a simple file as shown below.
这是一个简单的例子来说明它。我创建了一个简单的文件,如下所示。
I created a package and configured the flat file connection manager with below shown settings.
我创建了一个包,并使用下面显示的设置配置了平面文件连接管理器。
I configured the package with a data flow task to read the file and populate the data to a database table. When I executed the package, it failed.
我使用数据流任务配置了包以读取文件并将数据填充到数据库表中。当我执行包时,它失败了。
Clicked the Execution Results tab on the BIDS. It displays the same message that you have posted in your question.
单击BIDS上的“执行结果”选项卡。它显示您在问题中发布的相同消息。
[Flat File Source [44]] Error: Data conversion failed. The data conversion for column "Column 1" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
[Flat File Source [44]] Error: The "output column "Column 1" (128)" failed because truncation occurred, and the truncation row disposition on "output column "Column 1" (128)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
[Flat File Source [44]] Error: An error occurred while processing file "C:\temp\FlatFile.txt" on data row 2.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Flat File Source" (44) returned error code 0xC0202092. 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.
Hope it helps to identify your problem.
希望有助于识别您的问题。
Previous answer:
I think that the value in the last field on the last row of your file probably exceeded the value of OutputColumnWidth property of the last column on the Flat File Connection Manager.
我认为文件最后一行的最后一个字段中的值可能超过了Flat File Connection Manager上最后一列的OutputColumnWidth属性值。
Right-click the Flat File Connection Manager on your SSIS package. Click Advanced tab page on the Flat File Connection Manager Editor. Click on the last column and check the value on the OutputColumnWidth property.
右键单击SSIS包上的Flat File Connection Manager。单击平面文件连接管理器编辑器上的高级选项卡页面。单击最后一列并检查OutputColumnWidth属性上的值。
Now, verify the length of data on the last field of the last row in the file that is causing your package to fail.
现在,验证导致程序包失败的文件中最后一行的最后一个字段的数据长度。
If that is cause of the problem, here are two possible options to fix this:
如果这是问题的原因,这里有两个可能的选项来解决这个问题:
-
Increase the
OutputColumnWidthproperty on the last column to an appropriate length that meets your requirements.将最后一列的OutputColumnWidth属性增加到符合要求的适当长度。
-
If you do not care about truncation warnings, you can change the truncation error output on the last column of the Flat File Source Editor. Double-click the
Flat File Source Editor, clickError Output. Change the Truncation column value to eitherIgnore failureorRedirect row. I preferRedirect rowbecause it gives the ability to track data issues in the incoming file by redirecting the invalid to a separate table and take necessary actions to fix the data.如果您不关心截断警告,则可以更改平面文件源编辑器的最后一列上的截断错误输出。双击“平面文件源编辑器”,单击“错误输出”。将“截断”列值更改为“忽略失败”或“重定向”行。我更喜欢重定向行,因为它通过将无效重定向到单独的表并采取必要的操作来修复数据,从而能够跟踪传入文件中的数据问题。
Hope that gives you an idea to resolve your problem.
希望能帮助您解决问题。
#2
4
So I've come up with an answer. The other answers are extremely well thought out and good, but I solved this using a slightly different technique.
所以我想出了一个答案。其他答案都经过了深思熟虑和良好的解决,但我用一种略有不同的技术解决了这个问题。
I had all but eliminated the actual possibility of truncation because once I looked into the data in the flat file, it just didn't make sense... truncation could definitely NOT be occuring. So I decided to focus the second half of the error message: or one or more characters had no match in the target code page
我几乎已经消除了截断的实际可能性,因为一旦我查看平面文件中的数据,它就没有意义了......截断绝对不会发生。所以我决定关注错误消息的后半部分:或者一个或多个字符在目标代码页中没有匹配
After some intense Googleing I found a few sites like this one: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/6d4eb033-2c45-47e4-9e29-f20214122dd3/
经过一番激烈的谷歌搜索后,我发现了一些像这样的网站:http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/6d4eb033-2c45-47e4-9e29-f20214122dd3/
Basically the idea is that if you know truncation isn't happening, you have characters without a code page match, so a switch from 1252 ANSI Latin I to 65001 UTF-8 should make a difference.
基本上这个想法是,如果你知道截断没有发生,你有没有代码页匹配的字符,所以从1252 ANSI Latin I到65001 UTF-8的转换应该有所不同。
Since this has been moved to production, and the production environment is the only environment having this issue I wanted to make 100% sure I had the correct fix in, so I made one more change. I had no text qualifier, but SSIS still keeps the default Text_Qualified property for each column in the Flat File Connection Manager to TRUE. I set ALL of them to false (not just the column in question). So now the package doesn't see it needs a qualifier, then go to the qualifier and see <none> and then not look for a qualifier... it just flat out doesn't use a qualifier period.
由于这已经转移到生产,并且生产环境是唯一有这个问题的环境,我想100%确定我有正确的修复,所以我做了一个更改。我没有文本限定符,但SSIS仍将Flat File Connection Manager中每列的默认Text_Qualified属性保持为TRUE。我将所有这些设置为false(不仅仅是相关列)。所以现在包看不到它需要一个限定符,然后转到限定符并查看
Between these two changes the package finally ran successfully. Since both changes were done in the same release, and I've only received this error in production and I can't afford to switch different things back and forth for experimental purposes, I can't speak to which change finally did it, but I can tell you those were the only two changes I made.
在这两个更改之间,包最终成功运行。由于这两个更改都是在同一个版本中完成的,而且我只是在生产中收到了这个错误而我无法为实验目的来回切换不同的东西,我不能说最后做了哪个更改,但是我可以告诉你,这是我做的唯一两个变化。
One thing to note: the production machine running this package is: 10.50.1617 and my machine I am developing on (and most of the machines I am testing on) are: 10.50.4000. I've raised this as a possible issue with our Ops DBA and hopefully we'll get everything consistent.
有一点需要注意:运行此软件包的生产机器是:10.50.1617,我正在开发的机器(我正在测试的大多数机器)是:10.50.4000。我已经提出这个问题作为我们的Ops DBA可能存在的问题,并希望我们能够使所有内容保持一致。
Hopefully this will help anybody else who has a similar issue. If anybody would like additional information or details (I feel as if I've covered everything) please just comment here and let me know. I will gladly update this to make it more helpfull for anybody coming along in the future.
希望这将有助于其他有类似问题的人。如果有人想要更多的信息或细节(我觉得我已经涵盖了所有内容),请在这里发表评论告诉我。我很乐意对此进行更新,以使其对将来出现的任何人都更有帮助。
#3
2
It only happens on the one server? And you aren't using a test qualifier? We have had this happen before. This is what fixed it.
它只发生在一台服务器上?你没有使用测试限定符?我们之前已经发生过这种情况。这就是修复它的原因。
Go to that server and open the xml file. Search forTextQualifier and see if it says:
转到该服务器并打开xml文件。搜索TextQualifier并查看是否显示:
<DTS:Property DTS:Name="TextQualifier" xml:space="preserve"><none></DTS:Property>
If it doesn't make it say that.
如果没有说出来的话。
#4
0
I know this is a whole year later, but when I opened the flat file connection manager, for the text qualifier it had "_x003C_none_x003E_". I replace the "_x003C_none_x003E_" hex code garbage and put arrows like it should be, "<" none ">" (the editor is removing the arrows), and it stopped dropping the last row of the file.
我知道这是一整年后,但是当我打开平面文件连接管理器时,对于文本限定符,它有“_x003C_none_x003E_”。我替换“_x003C_none_x003E_”十六进制代码垃圾并放置它应该是的箭头,“<”none“>”(编辑器正在删除箭头),它停止丢弃文件的最后一行。
#5
0
I had the exact same error. My source text file contained unicode characters and I solved it by saving the text file using unicode encoding (instead of the default utf-8 encoding) and checking the Unicode checkbox in the Data Source dialog.
我有完全相同的错误。我的源文本文件包含unicode字符,我通过使用unicode编码保存文本文件(而不是默认的utf-8编码)并检查数据源对话框中的Unicode复选框来解决它。