Step 3: Adding and Configuring the Sort Transformation
In this task, you will add and configure a Sort transformation to your package. A Sort transformation is a data flow component that sorts data, and optionally applies rules to the comparison that the sort performs. The sort transformation can also be used to remove rows of data that have duplicate sort key values.
The sort transformation will sort the data extracted from the Excel spreadsheet by state and by city.
To add a Sort transformation
Open the Data Flow designer, either by double-clicking Data Flow Task or by clicking the Data Flow tab.
Right-click the path (the green arrow) between Data Conversion and Destination - Query and then click Delete.
In the Toolbox, expand Data FlowTransformations, and then drag Sort onto the design surface of the Data Flow tab, below Data Conversion. If Destination - Query is in the way, click it and drag it to a position lower on the Data Flow design surface.
On the Data Flow design surface, click Sort in the Sort transformation, and change the name to Sort by State and City.
Click Source - Query and drag its green arrow to Sort by State and City.
Double-click Sort by State and City to open the Sort Transformation Editor dialog box.
In the Available Input Columns list, first select the check box to the left of the State column, and then the select the check box by the City column.
The columns now appear in the Input Column list. State has the sort order 1 and City has the sort order 2. This means that the dataset is sorted first by state and then by city.
In the Input Column list, click the row that contains State. Click the Comparison Flags box, select the Ignore case check box, and then click OK.
Click OK.
Right-click Sort by State and City and then click Properties.
In the Properties window, verify that the LocaleID property is set to English (United States).