this caused me a big headache during a SSIS package troubleshooting…
The problems is that the SSIS in my local is referring to Nvarchar(100) . When it’s deployed to the staging box, the column becoame nchar(100) which caused the ssis package to fail… gotta be consistent.
Sometimes, SSIS package tends to remember older schema especially when connection manager is using expressions to be dynamic. Below is one example:
When you have modified schema on either source or target database, please make sure you’ve refresh .
For connection manager using expressions, you need to get rid of expressions and put a static first in order to refresh. Once it’s refreshed, put the expression back
Also, pay close attention to the initial value you put into variables. One example is like that: variable user::var_DBTimeZone comes from a table which contains dbTimeZone value. Currently its ‘vancouver’ which has 9 characters. But in the default value , I put ‘xxxxxxxx’ which has only 8 characters. Therefore, when package is running, it tries to put data ‘Vancouver’ into this variables User::var_DBTimeZone which triggers the truncation error.
######################################################################################################
Schema info across all SSIS environment have to be consistent. Schema info refers to table name and schema name, not the server or database info as these will
be stored in the dynamic connection manager!!!!
#####################################################################################################
in dynamic tsql, apostrophe was quite misleading, now, I tend to use Char(39) instead of '
