Hi I am trying to build a SSIS package with following table structure

table 1 (Partymaster)
PartyKeyId, PartyCode, Country

table 2 (ProductMaster)
ProductKeyId, ProductName, Qty

table 3 (Sales)
ProductKeyId, PartyKeyId, Qty

my source sales  table has data as follows
ProductName, PartyCode, Qty

Now how can i bring in the key ids for both product and the party in the sales table. please advise me on what transformation to use and how.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SThayaTechnical MAnagerCommented:
can you tell me your requirement what is the input and out put ,what kind of transformation you need
ValentinoVBI ConsultantCommented:
You're looking for the Lookup transformation.  Set up a data flow that takes your sales table as source, then hook up two Lookup transformations to fetch the ID fields from both PartyMaster and ProductMaster tables.  Set them to use Full Cache.

When building you're queries for the lookups, only retrieve the columns needed: the ID field and the field on which you're doing the lookup.  For PartyMaster that would be: select PartyKeyID, PartyCode from PartyMaster

Lookup transformation on MSDN:
Right...You are looking for a  LookUp transformation

If the tables are in the Same server, i would suggest you use the Join Statement to get the desired output as it is much faster than the Lookup transformation

You can also use Merge Transformation in case your tables are across multiple servers or different databases - MSSQL, Oracle etc (you will need to sort the columns before you join them using Merge transformation or you can also change the properties of the Merge transformation to order the Columns , if you want to avoid using Sort Transformation)
ValentinoVBI ConsultantCommented:
True about the Join statement, but I would really recommend to stay away from the Merge or Merge Join transformations unless you have no other option.  They have too much overhead, such as the "needs to be sorted" requirement.

But then again, it really depends on the situation.  There's a really nice explanation here:

In the case explained in the question, I'd go for either Lookup or Join statement.  After all, I'm not expecting any duplicates in "master" tables, serious data issue otherwise!

Additional reading material by SSIS expert Jamie Thomson:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Languages and Standards

From novice to tech pro — start learning today.