question

Turgor-9839 avatar image
0 Votes"
Turgor-9839 asked ·

Get data from duplicate rows in new columns

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
image.png (100.1 KiB)
image.png (57.2 KiB)
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Monalv-msft avatar image
0 Votes"
Monalv-msft answered ·

Hi @Turgor-9839 ,

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.




flatfilesource.png (27.2 KiB)
sort1.png (21.9 KiB)
sort2.png (15.4 KiB)
df.png (82.9 KiB)
· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Turgor-9839 ,

May I know if you have anything to update?

Best regards,

Mona

0 Votes 0 ·