SSIS 對數據排序

SSIS 對數據排序有兩種方式，一種是使用Sort組件，一種是使用sql command的order by clause進行排序。

一，使用Sort組件進行排序

SortType：升序 ascending，降序 descending

SortOrder：排序列的位置，從1開始依次遞增，

Remove wors with duplicate sort values：如果排序列重復，是否刪除重復的行，這不同于distinct，distinct是輸出的所有列不重復，選中該選項，只是保證排序列（輸出列的一部分）不重復。

該屬性可以從Sort Transformation Advanced Editor中查看和設置

二，使用sql command的order by clause對數據進行排序

Step1，使用OLEDB提供排序的數據，必須是經過排序的數據

select *from dbo.course c with(nolock)order by c.cid asc,c.score desc

Step2，打開OLEDB的Advanced Editor，查看Input and Output Properties選項卡

1，點擊OLEDB Source Ouput，設置IsSorted屬性為True，該屬性設置為true不會對數據排序，只是告知下游組件，該輸出數據已經排序。

如果將IsSorted屬性設置為True，實際數據并沒有排序，在package 運行時會出錯，所以必須提供已經排序的數據（在sql 子句中使用order by進行排序）

2，點擊Output Columns，逐個設置排序列（Order by Column_List)的SortKeyPosition屬性

SortKeyPosition屬性有Sort Position和Direction 兩個metadata：

正整數表示按照升序排序，0表示不是排序列，負整數表示按照降序排序，數字代表排序列的序號

例如以下的sql語句

select Col_1,Col_2,Col_3,Col_4from dbo.TableNameorder Col_1 asc, Col_2 desc,Col_3 desc

在Output Columns中需要逐個設置，Col_1,Col_2,Col_3,Col_4的SortKeyPosition
由于Col_1,Col_2,Col_3是排序列，序號從1依次遞增，而Col_4不是排序列，所以SortKeyPosition的配置如下

Col_1 的SortKeyPosition是 1，第一個排序列，且按照升序排序

Col_2 的SortKeyPosition是 -2，第二個排序列，且按照降序排序

Col_3 的SortKeyPosition是 3，第三個排序列，且按照升序排序

Col_4 的SortKeyPosition是 0，不是排序列

MSDN官方文檔

Sort Data for the Merge and Merge Join Transformations

In Integration Services, the Merge and Merge Join transformations require sorted data for their inputs. The input data must be sorted physically, and sort options must be set on the outputs and the output columns in the source or in the upstream transformation. If the sort options indicate that the data is sorted, but the data is not actually sorted, the results of the merge or merge join operation are unpredictable.

You can sort this data by using one of the following methods:

• In the source, use an ORDER BY clause in the statement that is used to load the data.

• In the data flow, insert a Sort transformation before the Merge or Merge Join transformation.

If the data is string data, both the Merge and Merge Join transformations expect the string values to have been sorted by using Windows collation. To provide string values to the Merge and Merge Join transformations that are sorted by using Windows collation, use the following procedure.

To provide string values that are sorted by using Windows collation

• Use a Sort transformation to sort the data.

  The Sort transformation uses Windows collation to sort string values.

  —or—

• Use the Transact-SQL CAST operator to first cast varchar values to nvarchar values, and then use the Transact-SQL ORDER BY clause to sort the data.

  Important
  You cannot use the ORDER BY clause alone because the ORDER BY clause uses a SQL Server collation to sort string values. The use of the SQL Server collation might result in a different sort order than Windows collation, which can cause the Merge or Merge Join transformation to produce unexpected results.

Setting Sort Options on the Data

There are two important sort properties that must be set for the source or upstream transformation that supplies data to the Merge and Merge Join transformations:

• The IsSorted property of the output that indicates whether the data has been sorted. This property must be set to True.

  Important
  Setting the value of the IsSorted property to True does not sort the data. This property only provides a hint to downstream components that the data has been previously sorted.

• The SortKeyPosition property of output columns that indicates whether a column is sorted, the column's sort order, and the sequence in which multiple columns are sorted. This property must be set for each column of sorted data.

If you use a Sort transformation to sort the data, the Sort transformation sets both of these properties as required by the Merge or Merge Join transformation. That is, the Sort transformation sets the IsSorted property of its output to True, and sets the SortKeyPosition properties of its output columns.

However, if you do not use a Sort transformation to sort the data, you must set these sort properties manually on the source or the upstream transformation. To manually set the sort properties on the source or upstream transformation, use the following procedure.

To manually set sort attributes on a source or transformation component

1. In SQL Server Data Tools (SSDT), open the Integration Services project that contains the package you want.

2. In Solution Explorer, double-click the package to open it.

3. On the Data Flow tab, locate the appropriate source or upstream transformation, or drag it from the Toolbox to the design surface.

4. Right-click the component and click Show Advanced Editor.

5. Click the Input and Output Properties tab.

6. Click <component name> Output, and set the IsSorted property to True.

   Note
   If you manually set the IsSorted property of the output to True and the data is not sorted, there might be missing data or bad data comparisons in the downstream Merge or Merge Join transformation when you run the package.

7. Expand Output Columns.

8. Click the column that you want to indicate is sorted and set its SortKeyPosition property to a nonzero integer value by following these guidelines:

   As an example of how to set the SortKeyPosition property, consider the following Transact-SQL statement that loads data in a source:

   SELECT * FROM MyTable ORDER BY ColumnA, ColumnB DESC, ColumnC                  

   For this statement, you would set the SortKeyPosition property for each column as follows:

• Set the SortKeyPosition property of ColumnA to 1. This indicates that ColumnA is the first column to be sorted and is sorted in ascending order.

• Set the SortKeyPosition property of ColumnB to -2. This indicates that ColumnB is the second column to be sorted and is sorted in descending order

• Set the SortKeyPosition property of ColumnC to 3. This indicates that ColumnC is the third column to be sorted and is sorted in ascending order.

• The integer value must represent a numeric sequence, starting with 1 and incremented by 1.

• A positive integer value indicates an ascending sort order.

• A negative integer value indicates a descending sort order. (If set to a negative number, the absolute value of the number determines the column's position in the sort sequence.)

• The default value of 0 indicates that the column is not sorted. Leave the value of 0 for output columns that do not participate in the sort.