When we use lookup component in MSBI and if look up does not find records it throws an exception. Yes you heard it right , if it does not find matching records it throws an exception.
Now this is bit weird for some people but that’s how works look up component in SSIS.
Solution:
The solution for this type of error is that when we are configuring the Lookup Component we get options as if the lookup does not find any matched records what to do at that time.
Here we see in the red box there are 4 options provided, ‘Ignore Failure’, ‘Redirect rows to error output’, ‘Fail Component’, ‘Redirect rows to no match output’.
Now by default if we see the option is set to Fail Component which means that if the rows are found with no matching entries then the look up component fails and the application fails i.e. no data is transferred to database.
We can select the option of Ignore Failure which means that no matching entries are found and the application will ignore that failure and move forward and complete the execution.
The option Redirect rows to error output is the option where if we want to know which entries are not matched during lookup we can create a simple error component and configure the connection so that all the unmatched entries are redirected here. Same for the other option of Redirect rows to no match output we can create a no match output component and redirect all the unmatched entries here.
This error we had received in the MSBI training drive program in Mumbai. You can check out Training page.
