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!



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: Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s