SSIS : Lookup component doesn't handle data type changes
I am always trying to optimise the storage I use this
often results in changing between tinyint, smallint and int data types as the
amount of data I need to capture increases.
Unfortunately the SSIS lookup component doesn't handle such changes very
well. Well it doesn't.
If you change the the data types of the column being used in the query of the
lookup component it will error saying that data types are incompatible. This is
due to inconsistency with the data types of the colum collections used in a
component.
I believe the lookup should handle this automatically, if you agree please
vote here.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=276217
The only work around is to recreate the lookup or to hack changing the SQL a
few times to get the lookup to change its internal structures.
-