Preview Content - log in or become a member

SSIS Lookup Transformation

Video

Did you find this video useful? [+1]  |   [-0]
Are you a member? Log In

Membership plans from just 6.00 USD*

* Currency: USD (US Dollar )
2500+ accounts so far

Plans:
Bronze
6.00 USD
Silver
9.00 USD
Gold
12.00 USD
6 Courses
T-SQL (3), SSRS (1), SSIS(1), Excel (1)
Watch Video
75 Mini Courses
T-SQL (34), SSRS (7), SSIS(16), + more
300+ videos and 300+ Articles
38
(part of
Courses)
Progress Tracking
21 Interview Q&As pages
T-SQL (8), SSRS (3), SSIS(4), Other (6)
Forum
11 Webinar Recordings
T-SQL (1), SSRS (9), Career(1)
1 DIY Project (beta)
70-461 Tests
Watch Video
60 days, 100% Money Back Guarantee
Instant Activation
Duration:
Bronze
Silver
Gold
Valid for: 1 month 6.00 USD
sign up
9.00 USD
sign up
12.00 USD
sign up
Valid for: 3 months
10% off
16.00 USD
sign up
24.00 USD
sign up
32.00 USD
sign up
Valid for: 6 months
20% off
28.00 USD
sign up
43.00 USD
sign up
57.00 USD
sign up
Valid for: 1 year
30% off
50.00 USD
sign up
75.00 USD
sign up
100.00 USD
sign up
Valid for: 2 years
40% off
86.00 USD
sign up
129.00 USD
sign up
172.00 USD
sign up
Valid for: lifetime
50% off
108.00 USD
sign up
162.00 USD
sign up
216.00 USD
sign up



Article

 

In this tutorial I will explain and give a simple example of SSIS lookup transformation.

Let's start with short explanation of the term lookup. Lookup takes input value and searches for a row in table that contains this value in the specified field; once it finds it you are able to extract values from different fields that belong to the same "row".

 Below is a simple example. I have a country table which contains field ID and country (right side). My input value is country with value UK and I want to get ID for this country.

Lookup takes UK then searches Country Field for the entry and when it finds it it returns the ID which 3.

ssis lookup transformation explanation

Now that we covered basics let's explain when you would use lookup. There are multiple scenarios however I will limit myself to explain one. Because you use SSIS you most likely are involved in building a data warehouse. During your extract you get source system key (business key) that you want to find in your dimension table and replace with ID (surrogate key) which will be used to load your Fact Table.

NOTE: This example is a simple lookup example using dimension table that does not track history so it should be used only for training purposes and specific scenarios. I will try to write more articles covering typical data warehouse lookups soon.

SSIS Lookup

In this section I will show you how to configure SSIS Lookup transformation (if you are new to SSIS check How to create SSIS package)

Create a new package add data flow in source control. Go to data flow and add source item; I will use OLE DB Source extracts Country field and I will use this field as input for lookup. See below screenshot of data flow and sample of data.

Are you a member? Log In

Membership plans from just 6.00 USD*

* Currency: USD (US Dollar )
2500+ accounts so far

Plans:
Bronze
6.00 USD
Silver
9.00 USD
Gold
12.00 USD
6 Courses
T-SQL (3), SSRS (1), SSIS(1), Excel (1)
Watch Video
75 Mini Courses
T-SQL (34), SSRS (7), SSIS(16), + more
300+ videos and 300+ Articles
38
(part of
Courses)
Progress Tracking
21 Interview Q&As pages
T-SQL (8), SSRS (3), SSIS(4), Other (6)
Forum
11 Webinar Recordings
T-SQL (1), SSRS (9), Career(1)
1 DIY Project (beta)
70-461 Tests
Watch Video
60 days, 100% Money Back Guarantee
Instant Activation
Duration:
Bronze
Silver
Gold
Valid for: 1 month 6.00 USD
sign up
9.00 USD
sign up
12.00 USD
sign up
Valid for: 3 months
10% off
16.00 USD
sign up
24.00 USD
sign up
32.00 USD
sign up
Valid for: 6 months
20% off
28.00 USD
sign up
43.00 USD
sign up
57.00 USD
sign up
Valid for: 1 year
30% off
50.00 USD
sign up
75.00 USD
sign up
100.00 USD
sign up
Valid for: 2 years
40% off
86.00 USD
sign up
129.00 USD
sign up
172.00 USD
sign up
Valid for: lifetime
50% off
108.00 USD
sign up
162.00 USD
sign up
216.00 USD
sign up


Did you find this page useful?
+8  |  -0
(8 Votes)