Distinct is a partially blocking component that remove duplicates from one flow. Its main advantages against the sort component provided in SSIS are:

  • Memory usage: Distinct does not cache the whole flow (as Sort does) but retains in memory only the distincts, consuming less memory then Sort
  • Distinct is partially blocking where Sort is fully blocking
  • Distinct is freeware, you can easily download sources and adapt it to your needs

A full description of the component and full sources can be downloaded at www.sqlbi.eu.

Originally appeared on: http://sqlblog.com/blogs/alberto_ferrari/archive/2007/02/08/distinct-values-in-ssis.aspx