SQL Server Bi step by step SSIS 4-Merge data 1

We have achieved the goal of importing data from Excel to the data stream, that is, the database. However, we only simulate data that does not repeat, that is, data that does not exist in the Database at this time. if the data in the Excel worksheet is the same as the primary key of the data in the database, an exception will occur when the data is inserted again. How can we achieve automatic update? That is to say, it automatically determines the data of the Data Source. If there is data in the database, update will be executed, and insert will be executed if there is no data in the database?
It may be called merging data. Here, the merging data and the merge component and the merge join component in the data streamDirectThe implementation effect is different. First, let's look at these two components.
Create a new package named mergedata, copy all the components in the previous foreachinput package in the control flow (including the Connection Manager), delete the foreach loop container, and modify the data stream, add two Excel Data catalogs, sort them by sort (by productnumber field), add merge for data merging, and add three data viewers respectively. The modified data flow diagram is as follows:

We will execute the following section of the three data viewer:

We can see that merge data combines the data from two data sources, but it does not directly achieve what we want to achieve. However, merge has the following two features:
1. Data must be sorted prior to merge conversion. This may be accomplished by using sort conversion as above, or directly specifying order by in the source connection.
2. the metadata between the two paths of the merged data must be the same.
3. It can only be applied to the merge of two data types. to merge more than two data types, you can select Union all for conversion.
What are the differences between the merge join component and the merge component? Let's create a new package of mergedata2 to check the merge join component. The two data partitions are connected to the product table and productinventory table respectively. The modified data streams are as follows:

The two sort instances are sorted in ascending order according to the productid field, and the input stream of the product path is output on the left. The merge jogin component is set to left external connections (left connections, external connections, internal connections are the same as SQL connections ), select the columns to be output to the path:

The data displayed in the three data viewers during execution is as follows:

You can see the connection with SQL, and combine the basic information of the product and the inventory information of the product, it is used to merge the outputs of two inputs and execute inner or outer connections to the data. Of course, if the two inputs are in the same database, you can directly use the join operation at the ole db data level to make it faster, but merge join can be used when you want to merge two different data sources.
OK, today we are here. Today we are familiar with merge and merge join, but we have not implemented the requirement for directly synchronizing and merging data (But in fact, you can consider it carefully. In fact, it can be implemented by combining other components.). We will use several methods to implement it in the next few times.

Download this project file. (For VS 2005)

 

Author: lone knight (like a year of water)
Source: http://lonely7345.cnblogs.com
The copyright of this article is shared by the author and the blog. You are welcome to repost this article, but you must keep this statement without the author's consent andArticleThe original text connection is clearly displayed on the page. Otherwise, the legal liability is retained.