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

Source: Internet
Author: User
Tags ssis

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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.