Merge Join

OLEDB SOURCE1:

Create Table OrderMaster
(
      OrderID Varchar(5),
      ClientCode Varchar(10),
      ProjectName varchar(25),
      OrderDate DateTime
)
INSERT INTO OrderMaster
SELECT 'A1001','CHEM02','NY WaterField',GETDATE() UNION ALL
SELECT 'A1002','ACCU01','Plainfield Soil',GETDATE()
GO

Flat File Source:

OrderID,SampleNumber,Matrix
A1001,A1001-01,Water
A1001,A1001-02,Soil
A1002,A1002-01,Water
A1002,A1002-02,Water

Oledb Destination:

Create Table OrderMasterAndDetail
(
OrderID Varchar(5),
ClientCode Varchar(10),
SampleNumber Varchar(10),
Matrix Varchar(10)
)
GO