(Conversion) SSIS _ Data Stream Conversion (Union all & merge join & merge)

Union all:Similar to the SQL Union all statement, tables are merged up and down without sorting.Union all ConversionSubstitutionMerge and convert: No sorting is required for input and output. More than two tables are merged.

Merge join:There are left join, inner join, and full join. Only two tables can be associated and sorted before connection.

Merge:Same as SQL Union, two tables are sorted before merging and merged up and down.

Create a table in the database. Create three TXT files to save the results.

 

Name	Course	Score
Zhang San	Chinese	90
Zhang San	Mathematics	83
Li Si	Chinese	74
Li Si	Mathematics	84
Li Si	Physical	94

Create a Data Flow task in the project. The data flow is as follows:

Note:

The data retrieved from the data source [ole db source] is:

 

Name	Course	Score
Zhang San	Chinese	90
Zhang San	Mathematics	83
Li Si	Chinese	74
Li Si	Mathematics	84
Li Si	Physical	94

The function of the [multicast] component is to distribute the input to one or more outputs, and each output is the same as the input.

Therefore, the content of the first multicast name [multicast Zhang sanli 4] is the same as that in the table above.

Each component is named by the name (Zhang San and Li Si) in the data table for ease of viewing.

The component [conditional split] is set as follows. The names that are equal to "Zhang San" and those that are not equal to "Zhang San" are output to two multicast sets respectively.

Among them, the content of the component [multicast only has three] is:

 

Name	Course	Score
Zhang San	Chinese	90
Zhang San	Mathematics	83

The content of the "Multicast only Li Si" component is:

 

Name	Course	Score
Li Si	Chinese	74
Li Si	Mathematics	84
Li Si	Physical	94

A. Next, let's first look at the component 【Union all] Is the theoretical result of the merger of the content of [multicast Zhang sanli 4] and [multicast only Zhang San ].

 

Name	Course	Score
Zhang San	Chinese	90
Zhang San	Mathematics	83
Li Si	Chinese	74
Li Si	Mathematics	84
Li Si	Physical	94
Zhang San	Chinese	90
Zhang San	Mathematics	83

B. Check the component again 【Merge join], Is the [sort _ Zhang sanli 4] and [sort _ LI 4] Two tables are sorted and then merged and connected. Merge Mode

Connection Type: left external connection. On the left is [sort _ Zhang sanli 4], which is the first input.

Condition (equivalent): On a. Name = B. Name and A. Course = B. Course

The output name is redefined.

The output theoretical results are as follows: (sorting is the sorting result by component [sort _ Zhang sanli 4)

 

Name left	Course left	Left score	Name Right	Course right	Right score
Li Si	Chinese	74	Li Si	Chinese	74
Li Si	Mathematics	84	Li Si	Mathematics	84
Li Si	Physical	94	Li Si	Physical	94
Zhang San	Chinese	90	Null	Null	Null
Zhang San	Mathematics	83	Null	Null	Null

C. Component 【Merge], Merge is 【Merge join] And [multicast only three] are merged after sorting.

The first input is [sort _ Zhang sanli 4 left join]. Because there are multiple columns (5 rows and 6 columns), the data structure prevails.

The second input is [sort _ zhangsan] (2 rows and 3 columns)

Merge and convert

It can be seen that the number of columns in [sort _ 3] is insufficient. Therefore, when merging with the table above, <ignore> means no value. Union is a Sort merge. The theoretical result is:

 

Name left	Course left	Left score	Name Right	Course right	Right score
Li Si	Mathematics	84	Li Si	Mathematics	84
Li Si	Physical	94	Li Si	Physical	94
Li Si	Chinese	74	Li Si	Chinese	74
Zhang San	Mathematics	83	Null	Null	Null
Zhang San	Mathematics	83	Null	Null	Null
Zhang San	Chinese	90	Null	Null	Null
Zhang San	Chinese	90	Null	Null	Null

Now, the design is complete, and now the compilation is complete! You can see the number of rows passed by the data stream. Result

Turn: http://blog.csdn.net/kk185800961/article/details/12378981

(Conversion) SSIS _ Data Stream Conversion (Union all & merge join & merge)