The query I use to load the LOOKUP cache does a type conversion
SELECT CAST(key_as_varchar_column AS INT) AS key_as_int FROM table
That works fine unless the source system GUI really allows text in this spot (it does) and user do enter text there (they do).
If you run the query in Management Studio with a "clean" table it will complete, on a "dirty" table it will run for some moments and abort in Management Studio with an error - as expected.
However, if you use the query above in a LOOKUP as source for the cache, it will NOT signal any error. The cache will be loaded up to just the row before the bad one, and the dataflow then use what's there.
This is very bad, I think. I'd much prefer an abort of my package instead of running on some undefined partially loaded cache.
On the SQL side, I can work with this using TRY_CAST and a WHERE on the query. This will allow all good rows to be cached, but still not inform of a problem.
Do I miss an option to make SSIS stop execution when the cache statement fails?
Asked by Ralf
(233 rep)
Jul 16, 2025, 02:48 PM