locked
remove duplicate using ssis RRS feed

  • Question

  • hi

    i need to remove duplicate values from my table

    for example i have id column

    id 

    2

    2

    3

    3

     i need to have just 1 row with unique id,i can do using execute sql task.

    but is there any other way

    Tuesday, February 26, 2013 7:24 PM

Answers

  • sort transformation has a deduplication option, but its a blocking transformation. With lots of data it could be slow... same for aggregation transformation.


    And you could even use a Script Component, but a TSQL query is probably the fasted.

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter



    • Edited by SSISJoostMVP Tuesday, February 26, 2013 7:46 PM
    • Proposed as answer by DeviantLogic Friday, March 1, 2013 10:34 AM
    • Marked as answer by Eileen Zhao Tuesday, March 5, 2013 5:44 AM
    Tuesday, February 26, 2013 7:41 PM
  • Hi Dude,

    If Your Source is SQL then what is the need of having SSIS Transformation for removing Duplicates, you can directly write SQl Query to remove the duplicate records rather than a transformation. Write the query and Execute that in SQL task to remove duplicate records and use dataflow task to transfer the data.

    Regards

    Naveen

    Wednesday, February 27, 2013 9:42 AM

All replies

  • sort transformation has a deduplication option, but its a blocking transformation. With lots of data it could be slow... same for aggregation transformation.


    And you could even use a Script Component, but a TSQL query is probably the fasted.

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter



    • Edited by SSISJoostMVP Tuesday, February 26, 2013 7:46 PM
    • Proposed as answer by DeviantLogic Friday, March 1, 2013 10:34 AM
    • Marked as answer by Eileen Zhao Tuesday, March 5, 2013 5:44 AM
    Tuesday, February 26, 2013 7:41 PM
  • The sort component has "remove duplicates" option

    Arthur My Blog

    Tuesday, February 26, 2013 7:44 PM
  • Hi Dude,

    If Your Source is SQL then what is the need of having SSIS Transformation for removing Duplicates, you can directly write SQl Query to remove the duplicate records rather than a transformation. Write the query and Execute that in SQL task to remove duplicate records and use dataflow task to transfer the data.

    Regards

    Naveen

    Wednesday, February 27, 2013 9:42 AM