Link to home
Start Free TrialLog in
Avatar of rajeeva_nagaraj
rajeeva_nagaraj

asked on

SSIS

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.

Regards
Rajeeva
Avatar of SThaya
SThaya
Flag of India image

can you tell me your requirement clearly....like what is the input and out put ,what kind of transformation you need
Avatar of ValentinoV
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: http://msdn.microsoft.com/en-us/library/ms141821.aspx
Right...You are looking for a  LookUp transformation
http://www.sql-server-performance.com/2009/SSIS-New-Features-in-SQL-Server-2008-Part1/

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)
ASKER CERTIFIED SOLUTION
Avatar of ValentinoV
ValentinoV
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial