Get data from duplicate rows in new columns

Turgor 1 Reputation point
2021-03-02T10:49:02.91+00:00

Hi I need to set up an SSIS package to import data from experiments (excel files) that are run in duplicate and triplicate this will be done on a multitude of files so it needs to be fully automated. I only need help with the data flow task not the foreach loop container task.

The table I have looks like this:

73386-image.png

As you can see under sample name there are samples that are run in duplicates and triplicates.

What I would like in the destination table would be this: Where the columns for CT, Well and Well position are pivoted so that each sample only has one row.

73392-image.png

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,485 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Monalv-MSFT 5,896 Reputation points
    2021-03-03T03:40:50.713+00:00

    Hi @Turgor ,

    We can use Sort Transformation in Data Flow Task to sort input data in ascending or descending order.

    The Sort transformation can also remove duplicate rows as part of its sort. Duplicate rows are rows with the same sort key values. The sort key value is generated based on the string comparison options being used, which means that different literal strings may have the same sort key values. The transformation identifies rows in the input columns that have different values but the same sort key as duplicates.

    Please refer to the following pictures:
    73641-flatfilesource.png
    73615-sort1.png
    73642-sort2.png
    73643-df.png

    Best regards,
    Mona

    ----------

    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.