SSIS - Incremental Load - Lookup Object

Question

I am building a SSIS project in order to create an ETL to load a Data Warehouse that uses a star schema model.

I've 3 dimensions:

 - Customer (Customer_ID, Customer_Name, Email)
 - Employees (Employee_ID, Employee_Name, Salary)
 - Product (Product_ID, Product_Category)

And my Fact Tables is very simples, just:

- Customer_ID
- Employee_ID
- Product_ID
- Sales_Amount

I'm trying to create a package using SSIS. I see a lot of webinars in order to learn how to make incremental load using SSIS and many of them suggest me that use the following objects structure:

In OLE DB Source I've the following query:

SELECT a.Customer_ID,
       b.Employee_ID,
       c.Product_ID,
       sa.Sale_Amount
FROM Staging_Area_table sa
LEFT JOIN Customer a ON
sa.Customer_Name = a.Customer_Name
LEFT JOIN Employee b ON
sa.Employee_Name = b.Employee_Name
LEFT JOIN Product c ON
sa.Product_Category = c.Product_Category

My questions is: 1) It gives me an error :) That one:

[Lookup [61]] Error: Row yielded no match during lookup.

2) If I want the rows that doens't match in Fact Table why I'm putting "Lookup Match Output" during the lookup objects

3) This is a good approach to load my fact table?

Thanks!!!!!

If you're looking up the keys in the source query, what are you doing in the lookup components? — billinkc, Jun 01 '17 at 03:51
I've a lookup for each Dimension and it see if the business Key exists in Source query. What should I've in the source query? — John_Rodgers, Jun 01 '17 at 08:08

score 2 · Answer 1 · answered Jul 10 '17 at 17:02

Answer to your first question -

You are getting the error because fail component is selected in lookup transaformation in the general tab. So you are getting error because there are no matched rows. To ignore the error select "Redirect rows to nomatch output in the general tab of lookup.

Second question - Many lookups are been needed because we have to make sure that all the dimentions are present before loading in fact tables.

Third Question - Yes this is the approach that many people follow.

SSIS - Incremental Load - Lookup Object

1 Answers1