I am using SSIS 2016 and Visual studio 2015 to create an SSIS package that just gets an excel document, and imports it into a SQL table, using the connection a variable (hope this makes sense or using correct vocabulary). However almost every other day or week, 1 column from the excel keeps erroring out, error message from SQL agent:
There was an error with Excel Source.Outputs[Excel Source Output].Columns[Notes] on Excel Source.Outputs[Excel Source Output]. The column status returned was: "Text was truncated or one or more characters had no match in the target code page.". End Error Error: 2019-06-09 07:30:05.72 Code: 0xC020902A Source: Admin Staging Import Excel Source  Description: The "Excel Source.Outputs[Excel Source Output].Columns[Notes]" failed because truncation occurred, and the truncation row disposition on "Excel Source.Outputs[Excel Source Output].Columns[Notes]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
the notes column is always the troubled column, its excel again, the type is NText, and the column for the SQL table is nvarchar(max), and the only way to get it to work, is open the SSIS package with my Visual studio 2015, add the excel file name to the variable name so the excel can recoginize it
( i left the variable blank because i have a foreach loop on the excel and import into SQL table) , then deploy the package and it works... has anyone run into this issue? how can i get around and have it work permanently? also, i tried to switch it from Data acces Mode: Table View to SQL command text and the SQL text is:
so... any advice or thoughts will only help, thank you in advance 🙂