Text field in ssis

• newbieuser

  SSCrazy Eights

  Points: 9423

  More actions

  October 13, 2016 at 9:10 pm

  #400866

  Hi friends,

  We are importing a table of about 70k records with a few fields >8000 characters in source file using SSIS to SQL Server database. Source xml file is a unicode and destination SQL Server table is ANSI. I changed the below steps in SSIS:

  1. In advanced editor for XML source - Changed Data type to Unicode text stream [DT_NTEXT] in external columns and output columns for the text field.

  2. In Data conversion editor- Changed Data Type to text stream [DT_TEXT] for the text field.

  But when I run the package, I get the error 'The conversion returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page' for the text fields. Looks like the DT_TEXT field has a limitation of 8000 characters in SSIS.

  Is there a way to import data >8000 characters from unicode to ANSI table in SSIS?

  Thank you so much in advance.