SSIS Merge Join Transformation vs Lookup Transformation

S#

Merge Join

Lookup

1 Partially Blocking(synchronous) Row Based (synchronous)
2 Two inputs One input.
3 One output Three Outputs:

  1. Lookup   match output
  2. No Match output
  3. Error output
4 No error output available Error output available.
No restriction on data sources. The Lookup transformation supports the following database providers for the OLE DB connection manager:

  1.  SQL Server
  2. Oracle
  3.  DB2
5 Specify the join is a FULL, LEFT, or INNER join. Only  Inner join
6 No caching option. Select the connection manager that you want to use. If you want to connect to a database, select an OLE DB connection manager. If you want to connect to a cache file, select a Cache connection manager.
7 Sorting required on the input columns Sorting not required on the input columns.
8 Provides option to join multiple columns but all the columns invloved in the join should be sorted. Provide the option to join multiple columns from the input with the lookup table. Sorting is not required
Advertisements

About Zaim Raza

7 Years of professional experience in Business intelligence using SQL Server and Microsoft SharePoint/.net Technologies. Experience in Data warehousing, Data Modeling, Data profiling, Data Integration, Data cleansing, consultations and enterprise reporting. I want to work for organization where I am able to utilize my skills, experience and knowledge to transform the technology into business value.
This entry was posted in SSIS. Bookmark the permalink.

3 Responses to SSIS Merge Join Transformation vs Lookup Transformation

  1. sorry I think lookup will not do a inner join rather it picks up first matching row in the lookup table

    • And one more lookup is case sensitive and merg join is case insensitive

    • Zaim Raza says:

      Lookup peforms the inner join with refrence data set.If there are multiple matches in the reference table, the Lookup transformation returns only the first match returned by the lookup query. If multiple matches are found, the Lookup transformation generates an error or warning only when the transformation has been configured to load all the reference dataset into the cache. In this case, the Lookup transformation generates a warning when the transformation detects multiple matches as the transformation fills the cache.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s