SSIS component conversion _ sorting, merging, and merging

I. Sort conversion (SORT)

The sort Conversion Function sorts the data in the input stream in ascending or descending order and copies the sorted data to the conversion output. It is similar to the order by statement in the T-SQL. The following uses the database adventureworks as an example to describe how to use sorting and conversion.

1. Create a package named "sortdemo", drag and drop a Data Flow task in the control flow, and create an ole db connector in the Connection Manager to connect to the adventureworks database.

2. In the variable toolbox on the left of the bids interface, set the variable count type to int32.

3. Drag and Drop an ole db data source on the data flow tab to connect to adventureworks. Select production. Product .:

 

4. Drag and Drop a sort conversion task. Set as follows:

 

 

 

The following describes how to use each attribute in the graph:

Attribute	Description
Available input Columns	Use this check box to specify the columns to be sorted
Name	Names of available input Columns
Transfer	Indicates whether the column is included in the sorting output.
Input Column	Select the columns to be sorted, or select from the available input columns.
Output alias	Sets an output alias for the input columns to be sorted. The default value is the input column name.
Sorting type	Indicates whether to sort data in ascending or descending order.
Sorting order	Indicates the sort order of columns. This item must be set manually and incremental data with the initial value of 1

In descending order by name, and then sort by productname in ascending order.

5. Drag and Drop a row count conversion to connect the sorting with it. The arrow points to the row count conversion. And set

 

The variable User: Count is created in the variable toolbox.

6. Create a viewer with a grid mesh on the green line between the sort conversion and the row count conversion. Then run the package. For example:

 

We found that the name and productname columns are output to the Data Pipeline According to our settings in the SORT conversion.

2. Merge and convert (merge)

Merging and conversion combines two sorted data sets into one dataset. Insert the rows to the output based on the values of the key columns of the rows in each dataset. The merge conversion function is similar to the Union all clause in the T-SQL statement.

Merging and conversion require that the input column have matched source data. In the SSIS designer, the merged and converted user interface automatically maps columns with metadata. You can then manually map other columns with compatible data types.

The following uses the production. Product table in the adventureworks database as an example to describe how to use merging and conversion.

 

1. Create a package named "mergerdemo", drag and drop a dataflow task in the control flow, and create an ole db connector in the Connection Manager to connect to the adventureworks database.

2. Create a variable count in the variable toolbox of bids, whose type is int32.

3. Drag and Drop two ole db data sources in the data stream. The connector connects to the adventureworks database. Select SQL commands for both data source access modes. The SQL statements are:

Select top 5 * from production. Product

Select top 6 * from production. Product

4. Drag and Drop two sort conversion functions in the data stream. Sort the productnumber columns of the two data sources in ascending order.

5. Drag and Drop a merge conversion to connect to the two sort conversions respectively. The settings are as follows:

 

 

 

6. Drag and Drop a row to count. It is connected to merging and conversion. Set variablename = User: Count

7. Create a data viewer for the connection between the merge conversion and the row count conversion. Then run the package. The result is as follows:

 

 

 

8. You can see part of the merged data in the pop-up box. You can continuously click the green triangle button to view the merged data. To complete the package execution. You can close the dialog box.

At this point, the use of the merge conversion function has been introduced. Some of the precautions will be introduced together with the merge join conversion.

Iii. Merge join)

The merge join conversion function is to join two sorted input columns to form an output dataset. It is similar to the connection query statement in the T-SQL statement. But it is different from this.

• To use a merged join, you must enter sorted data. The connection query in the T-SQL does not need

• If the two input columns are associated with the character type. You need to perform case-sensitive conversion or remove spaces. T-SQL connection query is not required.

The following describes how to use this information.

1. Create a package named "mergejoindemo" and drag and drop a Data Flow task in the control flow. Add an ole db connector to the Connection Manager. Used to connect to the adventureworks database.

2. Create a variable in the variable toolbox, for example

 

 

3. Drag and Drop two ole db data sources on the data flow tab. Connect to the "localhost. adventureworks" connector just created. Use SQL commands in data access mode. The SQL statements are as follows:

Select productid, productmodelid, name from production. Product

Select productmodelid, name from production. productmodel

4. Drag and Drop the two sort columns to the data flow tab, and sort the productmodelids of the Data columns of the two data sources in ascending order.

5. Drag and Drop a merge join conversion to connect to the two sort transformations respectively, and double-click the merge join conversion. In the displayed window, set the merge join conversion attribute according to the window.

 

 

In the figure, there is a join type option that contains three types: left Outer Join, internal join, and full outer join, which are equivalent to left join, inner join, and full join in the T-SQL, respectively.

6. Drag and Drop a row to count. It is connected to the merged join conversion. Set variablename = User: Count

7. Create a data viewer for the connection between the merge conversion and the row count conversion. Then run the package. The result is as follows:

 

In the pop-up box, you can see part of the merged data. You can click the green triangle button to view the merged data. To finish running the package, close the pop-up box.