Limiting character size of source column

Question

Hi,

I'm having a bit of an issue, i'm parsing through an XML source and receiving a truncation error when trying to output the column value because this particular string is more than 8000 characters, which is the maximum size that DT_STR will allow, is there a way that I can limit the column value to only take the first 8000 characters or would i have to put that limit in the XSL that i use to parse the XML file? any help on this problem would be greatly appreciated

Answer 1

If you do not foresee any problems in truncating your column value to 8000 characters then you can use a Simple Derived Column Transformation or a Data Conversion Task in your SSIS package to Trim your Column Value to 8000 Characters

(DT_WSTR, 8000)SUBSTRING([YourColumn], 1, 8000)
or
(DT_WSTR, 8000)[YourColumn] 
or
(DT_STR,8000, 1252)[YourColumn] -- if the columns are varchar or non unicode

SSIS will show warnings about potential data loass as you are truncating your source columns to fit a smaller destination

---

Regards, Dinesh

Answer 2

thanks for the reply, i solved the issue by adding a substring to the XSL that parses the XML file limiting the character limit of the element to 8000 character. DT_WSTR has a limit of 4000 character, DT_STR has a limit of 8000 characters, i was thinking about doing what you suggested, but since the xml is being parsed by the XSL into a file it wouldn't work, because when reading a file in SSIS the highest limit you can do is 8000 characters when reading the value into a column (unless you do a script task to override that). Simple answer is that limiting it to 8000 characters in the XSL worked for me.