DSXchange

Use copy stage with two outputs
1--> remove duplicates on all three cols --> transformer with constraint & filter u menioned --> join stage
2 --> Join stage, join on key1, key2

I have never worked on SQL server. But the question is, is it possible to have spaces in column identifiers ('A Key') in SQL Server?

If for some reason you need the past data then add a date field to data set.
In job1 set the derivation as sysdate for this field.
add filter on this field in the job2.

Execute the job after deactivating the run time column propagation(RCP) property of the stage.
If RCP is not required then for all the stages in the job.

Could you post the metadata of the target sequential file.

Try adding the below two options to your Environment variable and check if job completes succesfully

SKIP_UNUSABLE_INDEXES=TRUE,SKIP_INDEX_MAINTENANCE=TRUE

Remove the fail if lookup not found property and check if the result is same as the result of the join. then it most probably would be data problem.

Use the 'Orchestrate Schema Defintions' instead of 'Sequential File Defintions' under Import while you try to load column defintions to sequential file stage.

This wizard will let make you save the metadata in schema file as a Table defintion before loading it to any stage.