The lookup component solves join issues by using caching one of the datasets in memory and then compares each row arriving from the other dataset in its input pipeline against the cache. In the example below you can see the full-cache mode, whereby the lookup component stored all the rows resulting from a specified query in the memory. The benefit of this mode is that lookups against the in-memory cache are very fast.
So, first i started to build a package according to the below diagram:
First I build a query, something like this:
FROM Sales.SalesOrderHeader OH
This is the main stream of data that will flow trough the pipeline. It’s also a best practice to use the largest table for passing through a lookup transform with the data flow input. After this, open the Lookup transformation editor. And fill in the general tab like below:
Don’t forget to select the no matching entries.