I know this is an old question. The way I solved it - after failing by increasing the length or even changing to data type text - was creating an XLSX file and importing. It accurately detected the data type instead of setting all columns as
varchar(50). Turns out
nvarchar(255) for that column would have done it too.
1. On the OLE DB Source Editor - Error Output - Choose "Redirect Row" on the Column that is causing the failure
2. Add a new Flat File Destination text file to store the redirected rows
3. On the Data Flow Path Editor leading to the main output file - Data Viewers - Add a new OLE DB Source Output Data Viewer (Grid). Ensure the issue column is in the Displayed Columns list
At this point the SSIS package is now configured to re-direct any failed rows to a separate error log file.
Also enabled is a Debug Visual of the rows as they are loading (Data Viewer).
The issue with this package was only 4000 of the ~70000 rows were being sent to the main text output file. Checking this output file shows a partially loaded row at the end of the file. Querying the db showed a number of invalid characters in that row.
Remove the invalid characters or exclude that row from the results set to get the SSIS working again.
Convert all the char or varchar fields in the SELECT list in your stored procedure to nvarchar()
Then change the SSIS Flat File Connection Manager Code Page to "65001 (UTF-8)".
Then ensure the SSIS Data Flow Path Editor..Metadata..Code Page = 0 (not 1252 which is ANSI))
This makes the db return Unicode data and the output file is also Unicode/UTF-8
(UTF-8 is a character encoding capable of encoding all possible characters, or code points, in Unicode. The encoding is variable-length and uses 8-bit code units)
Also, confirm your output file us indeed in UTF-8 encoding. Open the file in Notepad, click 'Save As...', in the 'Encoding:' box you will see the current file format.
Or you can follw below steps
1. When the "Choose a Data Source" window pops up,
2. On the left-hand side, there is a column called "Advanced". (The wizard only allows you to click on "Advanced" when there is a Data Source selected already)
3. Click on the Advanced, another window shows up, on the right hand-side there is a column called "Msc",
4. Change the DataType in the dropdown menu to "text stream [DT_TEXT]".
N.B: This error happens when you have created a table with different columns containing different Data Type and you try to import data mapped to the column with different Data Type. In the video below, you will see the demonstration to attemp to import data to a table with changed column data type.