Select which rows are written during SSIS error logging tasks

Question

If I try to insert two non-distinct rows using SSIS into a table with a primary key how can I determine which row is written to the table and which one is handled by the error logging task?

For example, I have a destination table with two columns and a primary key constraint and I'm trying to insert two rows from the source table that have the same values but no primary key constraint.

(KeyColumn UNIQUEIDENTIFIER NOT NULL, TextColumn VARCHAR(50) NULL)

If I have two rows that have the same primary key, trying to insert both would crash the SSIS package but if I add error logging to record the duplicate row I have a 1/2 chance of logging the incorrect record

| KeyColumn  |   TextColumn  |
|     1      | 'ValidRecord' |
|     1      |'InvalidRecord'|
|     2      | 'ValidRecord' |

Provided I can consistently determine the valid record (eg: TextColumn should never contain the text 'Invalid' ) how can I create a SSIS task to always write the correct record to my SSIS table and send the incorrect record to the logging task?

Answer 1

You can achieve this using 2 approaches:

(1) Using a Script Component

If you need to check the duplicates based on the KeyColumn (first occurence is valid others are invalid), you can use a script component to achieve that.

1. First, Add 1 output column of type DT_BOOL (Example Name: Flag)

2. Inside the Script use a similar script:

   using System.Collections.Generic;
   
   public class ScriptMain:  
       UserComponent  
   
   {  
   
       List<string> lstKey = new List<string>;
   
       public override void Input0_ProcessInputRow(InputBuffer0 Row)  
       {  
   
           if(!lstKey.Contains(Row.KeyColumn.ToString()){
   
               lstKey.Add(Row.KeyColumn.ToString());
               Row.Flag = true;
   
           }else{
   
               Row.Flag = false;
   
           }
   
       }  
   
   }
   

3. Add a conditional split to split rows based on Flag column value, using a similar expression:

   [Flag] == True
   

4. Map the True path to the destination table and the False path to the error logging destination

(2) Using a conditional split

If you can check if the orw is valid based on the text column, then you can add a conditional split with the following expression:

FINDSTRING([TextColumn],'InvalidRecord',1) == True