dataflow - SSIS - How do I join two sources on a complex expression? -
i have 1 data source has column part_number.
my second data source has product_family_id, product_id , part_mask.
mask pattern string used in expression part_number mask. instance 'nxa%' or '001-[abcd][456]9-121%'
usually legitimate part numbers product family based on available product part masks, in instance need go in other direction. based on part number, must find related products in product family , store in summary table.
simulating in t-sql:
declare @partlist table (partnumber varchar(100)) insert @partlist (partnumber) values ('nxampvg1') select distinct pl.partnumber, match.product_id @partlist pl join ( select m.masks, p.product_id mcs_productfamily_partmasks m join product p on m.productfamilyid = p.productfamily_id) match on pl.partnumber match.masks
desired output:
part_number product_id ----------- ---------- nxampvg1 15629 nxampvg1 15631 nxampvg1 15632 nxampvg1 15633 nxampvg1 15634 nxampvg1 15635 nxampvg1 15636 nxampvg1 15637 nxampvg1 15638 nxampvg1 15639
how can accomplish in ssis data flow task?
create 2 data sources, 1 gets part numbers source 1 , gets product id's, product family's , masks using inner query select m.masks, p.product_id mcs_productfamily_partmasks m join product p on m.productfamilyid = p.productfamily_id
source two. data destinations these should staging tables on same sql server. use derivation of sql stated in example in such way: select distinct pl.partnumber, match.product_id stg_source_one pl join stg_source_two match on pl.partnumber match.masks
Comments
Post a Comment