I ran into this problem today that didn’t take a long time to resolve, but took me a little by surprise. Most of my SSIS packages use the Lookup transformation component to look up keys used in source systems and match them to the data warehouse. When SSIS packages fail at the Lookup component, the most common cause is that the source system’s key does not exist in the data warehouse and must be added first. This time was different, however.
The error I found in the package output was this:
The "L_BlackboardCourseId" failed because truncation occurred, and the truncation row disposition on "L_BlackboardCourseId.Outputs[Lookup Match Output].Columns[BlackboardCourseId]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
This is the first time I recall seeing a truncation error on a Lookup. It’s a bit odd after all, that a value you look up in a database would be truncated in the SSIS pipeline when the SSIS pipeline obtained the metadata from that same database. In addition, when a source or destination component’s metadata no longer matches the actual source or destination, there’s a warning shown at design time.
In this case, I had (several months ago) increased the column size of a value column in a lookup table from 25 to 50. This was necessary to accommodate larger business keys after an operational change. Unlike source or destination components, the Lookup component apparently does not warn at design time that the cached metadata is out-of-date. The first time the package ran and attempted to retrieve a value from the Lookup that was larger than 25 characters, the truncation error occurred. I didn’t remember that I had increased that column’s size, so it took a few minutes of database schema review and SSIS pipeline metadata review to realize what was happening.
After I had found the cause, the solution was quick: edit the Lookup component and just click OK to close the dialog. The SSIS pipeline showed the updated length as 50 and the package executed successfully.
Sven Aelterman 