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)