If we use Sort transformation for large data sets, performance issues will come. So we need to avoid sort transformation.
Example:
- Edit the source (excel or OLEDB source) and change the “Data access mode:” to “SQL Command”.
- Writ the select statement for source table with order by (on required fields).
- Click “OK”.
- Right click on the source and select “Show Advanced Editor” option.
- “Advanced Editor for OLEDB Source” window will open. Click on “Input and Output Properties” option.
- In the “Inputs and Outputs:” section, select the “OLEDB Source Output”.
- Right side of the window “Common Properties” will enable.
- Set “IsSorted” property as true.
- In “Inputs and outputs:” section expand the “OLEDB Source Output” list.
- Expand the “Output Columns” list. And select ID column because we need to sort the source data based on ID column.
- ID column “common Properties” list set the “SortKeyPosition” as “1”.
- If you want to add one more column in sorted list. Select required column and set the "SortKeyPosition" value "2".
- Click “OK” button to complete the sorting.
- One sorted data set is ready.
- Same way we can prepare remaining required sorted data sets, without using sort transformation.
