How to truncate text string in SSIS

I was working on a solution to read table data from .csv file and push into a SQL table. I started receiving error when one of the Column text had 8000+ characters. I just need to push only 1000 characters to SQL table and truncate remaining characters. I spent hours to address the problem, following is quick refresh during the investigation and fix!

  • While reading from a text file, SSIS String column type (DT_STR) can process only 8000 characters. We need to use TEXT column type to read a column with 8000+ characters.
  • String operations cannot be performed on Stream (DT_TEXT data type)
  • Finally I had to use SSIS feature to ignore the error while truncating the text. You may ignore the error only at specific column!
  • Following Pic explains step by step process!

Truncating8000PlusCharsInSSIS

Reference: http://stackoverflow.com/questions/28455684/ssis-converting-dt-textlength-11-000-characters-to-dt-str-and-trim-to-1-000/28628046#28628046

Advertisements
This entry was posted in SQL, SQL Error, SSIS and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s