Before I provide a general essay, I'd like to confirm if you would like me to:
VARCHAR(255)
NVARCHAR(100)
: Try connecting to the data source outside of SSIS using the same credentials and connection string to isolate the issue. SSIS-652