I was having trouble importing a client spreadsheet and using the lookup component in the data portion of SSIS.
The original data had some bad rows and these were causing the error message:
component failed because error code 0xC020901E occurred
I went into edit for each of the lookup components and cofigured it to ignore errors.
Before the lookup I dropped any FK constraints that might depend on those lookups, and made those foreign key columns nullable. After the data was imported I ran somecleanup code to remove the columns where the lookup failed, reapplied the not null constraint and added the foreign key relationship back in.
This worked for tables where I was doing a single column lookup. But for one that needed three lookups on the way, I got data in with this approach - but it was not good enough quality.
I may have to resort to writting my own importer for this part of the process.
