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!