1

I have a situation in which I need to import data from one table to another. The challenge here is in tables I have some columns which are references to other table values (kind of foreign key). I need to match these referencing columns and if fields match I need to map the respective id otherwise I need to insert that value in the referencing table and than map id in the source table data source and import all the data.

Elaborating my problem:

ETL with Lookups

So this is how my ETL look like. Currently I am "On Lookup Match" moving forward with execution but I also need to create new records in the respective table if "Look up don't match". (And this I need to do for all the 8 lookups I am doing). I do not understand how to do all this in sequence and import all the data at final stage.

  • That sounds like exactly the sort of ETL process SSIS is intended to help with, but you'll need to specify your needs in more detail to get much help beyond "yes, SSIS will do that" - your question is a bit to vague/open to get specific help as it currently stands. – David Spillett Feb 16 '15 at 11:50
  • Hi David, Thanks for the quick response. I have re- explained my problem with real ETL diagram. Please explain me how to solve this problem. – Mihir Chittora Feb 17 '15 at 19:29
  • Essentially you just need to set your lookups to on no match Redirect row and then move to flow accordingly. – Zane Feb 18 '15 at 18:22
  • Here in my situation, suppose in my data set i have 3 rows whose column(suppose partner) i am lookup in "Partner Table". If these 3 rows partner are same and does not match in look up, i just need to insert one time the corresponding partner. But in my case it is inserting partner 3 times. – Mihir Chittora Feb 19 '15 at 16:35
1

Here is what i did:

  1. Created a new data flow to look up and insert non matching fields.
  2. Inserted the actual data flow.
0

As David points out above the requirement is a bit vague, but if I understand you correctly, you wish to import data that has look up values in the source system, and if those lookup values are missing from the target system then you wish to add them in the target system. You can achieve this by using a lookup transform within your data flow task.

Specifically, you can add a Lookup No Match Output on your lookups. That flow can then connect to an OLE DB Destination or SQL task in order to add the data that didn't match.

Lookup with a No Match Output

Robert Sheldon has written a good tutorial on this that you can find on the Simple Talk website at https://www.simple-talk.com/sql/ssis/implementing-lookup-logic-in-sql-server-integration-services/

  • Hi Nigel, Thanks for the quick response. I have re- explained my problem with real ETL diagram. – Mihir Chittora Feb 17 '15 at 19:28
  • Here in my situation, suppose in my data set i have 3 rows whose column(suppose partner) i am lookup in "Partner Table". If these 3 rows partner are same and does not match in look up, i just need to insert one time the corresponding partner. But in my case it is inserting partner 3 times. – Mihir Chittora Feb 19 '15 at 16:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.