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

We have introduced three methods to merge data streams and database data, that is, synchronize data in Excel with data in the database, update existing data, and add data that does not exist, in addition, it can be expanded to delete data that does not exist in the Excel data source. next we will introduce other methods to achieve this goal.
We have introduced in data merging 1 that direct implementation via merge and merge Jone cannot be achieved directly, but we can implement it through a work und. first, we create a new package, mergedatamerge. As before, prepare the control flow, variables, and Connection Manager as before. in fact, just like mergedata2 in merged data 1, the Excel data source and database table data are merged according to the productnumber field.

Here we chose a full external connection to completely merge the data on both sides. if the data of one party does not match, null is used. therefore, we can directly add, update, or delete data in this row based on the merged productnumber and newproductnumber. below the merge join component, we add the conditional split component, which is used to determine the branches for different situations:

When the productnumber in the row is null, it indicates that no matching data is found from the database. Therefore, this data should be added. When newproductnumber is null, the data corresponding to productnumber in the database does not exist in the Excel Data Source. Therefore, this data item should be deleted. At last, if the two productnumbers are the same, update the data item. note that the two conditions for determining isnull must be put in front, because trim is used in the third one. If it is null, an error occurs.

Next, we will introduce how to use SCD component to implement this. First, we will create a package of mergedatascdcomponent. Similarly, we will prepare other resources, then drag the slowly changing dimension component in and double-click the control to set it, we will find that this is similar to an installationProgramYou can set it step by step and click Next. First, we need to select the Data Connection Manager and then set the business key. Here we set the productnumber column as the business key:

Click Next. Here we can select the columns in the data source and set the change type of this column, which can be fixed, changed, or historical. Detailed descriptions of different types in the column on the left are as follows:

We can see that this component is very powerful, so we can make so many differences for different types of changes. Then we click Next and we can see that we can set it here, if the conversion fails when a fixed type of attribute is changed and all matching records are changed when the changed attribute is changed.

Next, complete the configuration. We can see that three branches are added under slowly changing dimension, just as we did in the previous implementations, however, all configurations are completed this time. although the two component Names below are displayed in English (it is estimated that there is no corresponding Chinese translation), they are still powerful.

Let's take a look at inserting the target component. All that is automatically generated is an ole db target component, and the fields have been mapped, some special columns have been automatically ignored (considerate ). the other two update operations are the generated ole db Command component and the SQL statement is automatically generated.

We ran a package and found that we could automatically judge whether the data was updated or insert it:

Now, the data merging process is complete.

Download this project file. (For VS 2005)

 

author: lonely Knight (like the water of the year)
Source: http://lonely7345.cnblogs.com
the copyright of this article belongs to the author and the blog park a total, welcome to reprint, however, this statement must be retained without the author's consent and the original article connection is clearly displayed on the Article page. Otherwise, the legal liability shall be held accountable.