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
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
can you tell me your requirement clearly....like what is the input and out put ,what kind of transformation you need
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
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)
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.