Handling Duplicate records

Ambertje

Hello everyone, I came across a problem when I loaded my records from SSIS to SQL DB. My source file is a txt file. I use 5 fields to make my records unique but unfortunately there are some records that can't be made unique. Even the records that aren't unique must be loaded into my table although in SSIS the duplicate records are written to an error log and are excluded out of the load into the DB. This is a small example of a few records: record 1 and 2 are unique, 3 and 4 not.

HEADER WH DATE_CREATED TIME_CREATED PO RECEIPT ARTICLE QTY_PBS CW CODE_DATE_RECEIVED SYSTEM_FLAG QTY_PBL FLOW_RECEIVING_FLAG FLOW_DEMAND_FLAG VENDOR_ID SHIPPING_UNIT WH_FROM AANT_PALLETS SSCC_NR EMPTY_FACTURATION_FORM
1010 3 30/07/2014 11:49:24 861819693 71663 1610570 4 36507 18/08/2014 S 0 N N B986 77000 0 7 054001090102087337
1010 3 30/07/2014 11:49:24 861819693 71663 6750 1 0 18/08/2014 S 0 N N B986 77000 0 7 054001090102087337 I
1010 3 30/07/2014 11:49:24 861819693 71663 6681 4 0 18/08/2014 S 0 N N B986 77000 0 7 054001090102087337 I
1010 3 30/07/2014 11:49:24 861819693 71663 6681 16 0 18/08/2014 S 0 N N B986 77000 0 7 054001090102087337 I

In SQL I use a View to transform the data and load only the data I need from the txt file:

ALTER VIEW [dbo].[F_Received_Colli_VW]
AS
SELECT
CAST(NULL AS Numeric(8, 0)) AS DT_Start_Dates
, CAST(NULL AS NVARCHAR(6)) AS TI_Start_Times
, CAST(NULL AS Numeric(8, 0)) AS DT_Code_Dates
, NULL AS FK_Article
, NULL AS FK_FlowType

, CAST(CAST(RIGHT(dbo.AD.DATE\_CREATED, 4) + SUBSTRING(dbo.AD.DATE\_CREATED, 4, 2)
    + LEFT(dbo.AD.DATE\_CREATED, 2) AS int) AS Numeric(8, 0))                                AS CREATE\_DATE

, CAST(dbo.LPAD(LEFT(dbo.AD.TIME\_CREATED, 2) + SUBSTRING(dbo.AD.TIME\_CREATED, 4, 2)
    + RIGHT(dbo.AD.TIME\_CREATED, 2) , 6, '0') AS NVARCHAR(6))                               AS CREATE\_TIME

, CAST(CAST(RIGHT(dbo.AD.CODE\_DATE\_RECEIVED