Search code examples
ssisflat-file

SSIS - Perform group by and count on flat file

I make some transformations and validation on a flat .CSV file to import data. I'd like to add a column with the number of time an employee is present in the flat file for exemple :

Input Data Flow :

Input sample

Output Data Flow :

Output sample

I don't know how to transform my dataflow... have an idea?

Solution

  • This is how I would do it:

    1. If your data is not already sorted, sort it on Employee_Id.
    2. Use a Multicast to split your data flow into two streams.
    3. In one of the streams, add an Aggregate transformation that Groups by Employee_Id and adds a new count column that contains COUNT(*) for each Employee_Id. Time will be ignored and discarded in this stream.
    4. Merge Join the two streams back together on Employee_Id, keeping only the Count column from the aggregated stream.

    This should leave you with the desired output of one row for every row in the source data, but with the Count per Employee_Id on each row.