3

I am trying to upload a CSV file (comma delimited) to a SQL Server 2012 (vs 11.0.2100.60) table. I have created a table with all columns equal to nvarchar(MAX) for test purposes as I just want to get the data into the database just to test. Unfortunately, I keep getting the following error:

Messages Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "Column 2" 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 - Book2_txt.Outputs[Flat File Source Output].Columns[Column 2]" failed because truncation occurred, and the truncation row disposition on "Source - Book2_txt.Outputs[Flat File Source Output].Columns[Column 2]" 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 "C:\Users\shalinp\Desktop\MISO\junk\Book2.txt" on data row 3. (SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Source - Book2_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)

My file is large but I created a new file that only has the first 4 rows of data to test. Unfortunately stackoverflow does not let you upload a file so I can't provide it (correct me if I am mistaken?).

But here is a cut and paste of it:

1/1/2010,ARNLD HAZELTON BASE,HAZLTON HAZLTARNOL34_1 1 (LN/ALTW/ALTW),ACTUAL,1,($23.18)
1/1/2010,MANISTIQ_T1_T1_XF,MANISTIQ T1 T1 (XF/WEC/*),,1,($12.47)
1/1/2010,ONT-NYIS BASE,BECK2 BECK2GPACKAR_1 A (LN/ONT/NYISO); BECK2 BECK_BNIAG_3_1 A (LN/ONT/NYISO); BECK2 BECK_ANIAG_3_1 A (LN/ONT/NYISO); STLAWRE1 SAUND_MOSES1_1 A (LN/ONT/NYISO); BECK2 BECK2GNIAG_3_1 A (LN/ONT/NYISO); STLAWRE1 SAUND_MOSES__1 A (LN/ONT/NYISO),ACTUAL,1,($6.05)
1/1/2010,PADDCK_TWNLINE FLO PADDCK_BLACKHWK,TOWNELIN TLR_PAD 1 (LN/ALTE/ALTE),PADDOCK-BLACKHAWK 138 (X-53),1,($3.06)

I suspect the failure is occurring in row 3, column 3, which has a long text string:

BECK2 BECK2GPACKAR_1 A (LN/ONT/NYISO); BECK2 BECK_BNIAG_3_1 A (LN/ONT/NYISO); BECK2 BECK_ANIAG_3_1 A (LN/ONT/NYISO); STLAWRE1 SAUND_MOSES1_1 A (LN/ONT/NYISO); BECK2 BECK2GNIAG_3_1 A (LN/ONT/NYISO); STLAWRE1 SAUND_MOSES__1 A (LN/ONT/NYISO)

That said, this is why I'm using type nvarchar(max). In any case I appreciate the help.

Thank you.

codingknob
  • 11,108
  • 25
  • 89
  • 126
  • from the data you pasted, I am having a hard time telling where the row begins and where it ends... can you elaborate on that pls. – SQLGuru Feb 01 '13 at 06:17
  • are you importing the data using the import wizard, or using a script – SQLGuru Feb 01 '13 at 06:17
  • @SQLGuru - Thanks for your speedy response. So each row ends at the end of the dollar value. There should be a LF there. – codingknob Feb 01 '13 at 17:24
  • @SQLGuru - I'm using the import wizard. – codingknob Feb 01 '13 at 17:53
  • I also tried to replace the text string in row 3 column 3 with a hand written string that is equally long in length and I get the same error. I would have thought the *nvarchar(max)* type would have been sufficient? – codingknob Feb 01 '13 at 19:22
  • this solves the problem = http://stackoverflow.com/questions/14212536/sql-server-import-wizard-fails-with-incomprehensible-message – codingknob Feb 01 '13 at 19:46
  • No wonder Jim Rogers is short MSFT stock. – codingknob Feb 01 '13 at 19:47
  • This link shows the solution to the problem. http://stackoverflow.com/questions/14212536/sql-server-import-wizard-fails-with-incomprehensible-message – codingknob Feb 01 '13 at 19:49

0 Answers0