November 8, 2016 at 7:56 am
Hello,
This is really driving me nut:
I have a pipe delimited text file to be imported into a new database/table, I got an error saying:
Executing (Error)
Messages
Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "MNACSGMT" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)
Error 0xc020902a: Data Flow Task 1: The "Source - ADSC GL00100_TXT.Outputs[Flat File Source Output].Columns[MNACSGMT]" failed because truncation occurred, and the truncation row disposition on "Source - ADSC GL00100_TXT.Outputs[Flat File Source Output].Columns[MNACSGMT]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)
Error 0xc0202092: Data Flow Task 1: An error occurred while processing file "F:\Vancouver\T\TVI Inc\ClientData\Raw Data\GL00100\ADSC GL00100.TXT" on data row 2.
(SQL Server Import and Export Wizard)
Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Source - ADSC GL00100_TXT 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.
(SQL Server Import and Export Wizard)
Well, since it indicates there is some problem with data row 2, I inspect the data right away and it is just normal to me. Anyway, I increase the size for the reported failing column MNACSGMT from varchar(50) to varchar(255), I also went through preview, the preview shows me the data, but again, same error message was thrown to me.
Can anyone shed me more light on how to fix it? Thank you very much.
November 8, 2016 at 8:17 am
Did you create the import from scratch, again? It might have kept the length in the package even if the column length was increased.
November 8, 2016 at 8:58 am
Luis Cazares (11/8/2016)
Did you create the import from scratch, again? It might have kept the length in the package even if the column length was increased.
Yes, I tried a few times from scratch, I have the same feeling that the length was indeed NOT increased, but I increased it from the beginning to ensure that there shouldn't be a truncation needed.
November 8, 2016 at 9:11 am
I tried it again and increased the column size in Choose a Data Source->Advanced, I got the following errors:
- Validating (Error)
Messages
Error 0xc02020f4: Data Flow Task 1: The column "Company" cannot be processed because more than one code page (65001 and 1252) are specified for it.
(SQL Server Import and Export Wizard)
Error 0xc02020f4: Data Flow Task 1: The column "ACTINDX" cannot be processed because more than one code page (65001 and 1252) are specified for it.
(SQL Server Import and Export Wizard)
.....
November 8, 2016 at 9:31 am
Go To Flat File Connection Manager and then Advanced.
Increase the column size there. And then do Save All.
Also ensure you have a Text Qualifier. Generally doubleQuotes.
it's possible a pipe symbol may have come in between the text and that shifts all the values.
Vinay
November 8, 2016 at 9:32 am
Just to double check using Luis suggestion, I did it again from scratch and it works now, I deleted all the previous tables (generated but with 0 rows), from the previous error messages I know which columns are to be increased size, and I increase the size from Choose a Data Source->Advanced, this time I do not receive error and the data is imported.
I do believe there is some buggy logic in the import wizard that causes error like this.
Thank you Luis for your enlightening.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply