SQL Server Bi step by step 4-3 Merge data conditional split and slowly changing Dimension components

Merge data 3 ----------- conditional split and slowly changing Dimension components

Search for Excel and databases using productnumber. When the data is in Excel but not in the database, it is added; when all data is in Excel, it is updated; if there is data in the database but there is no data in Excel, It is deleted.
Delete the database if it is no longer in the database.

I. Conditional split component
If you use the product table for testing, there will be a lot of trouble when adding: Because full join will cause multiple null values in a record, the database must not be empty! In this case, add is not allowed! So I only need to test a new set of data ~ 5555 ~ It took me a lot of time.

 

1. Prepare an Excel Data source. The data comes from the current database, with a total of nine rows of data.
However, the productnumber data with ID 1, 2 is modified to add.
The productnumber record with ID 3 and 4 exists in the data, but the makeflag attribute has been changed for update.
In the data table, we change the productnumber value in the table to one that does not exist in the Excel table. To enable the delet value to be equal to 1, there are seven rows of data after deletion.

2. Create a mergedatamerge package.
3. Drag an ole db source and an Excel source. The ole db source is connected to the producttable, And the excelsource is connected to product1.xls.
4. Drag two sort components to sort them in ascending order by the productnumber field.
5. Add the merging component.
 
Productnumber indicates the ole db field. Newproductnumbe is an Excel 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, use null instead. therefore, we can directly determine whether to add, update, or delete this row of data based on the merged productnumber and newproductnumber. under the merge join component, we add the conditional split component, which is used to determine the branches in different situations.

6. Conditional split component
 
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.

 

I have not completed the update task and cannot find it. Let's see it later.
Solution:

7. Configure ole db destination and ole db command.
Update Statement of ole db command

Script

Update [DBO]. [product_teste] Set [makeflag] = ?, [Name] =? Where [productnumber] =?

Configure column ing:
 
Be sure to pay attention here! The order of input columns and the order of parameters in the update statement must be consistent! Otherwise, an error is reported.
Run:
 

Error when no derived Column exists
 

Run:

 
 

 

 
 
 

Merged database
 
Original DB
 

2. Use the slowly changing dimension component for the same purpose.
1. Prepare the data source. But the last line of DB data has changed. New package: mergedatascdcomponent. dtsx
 

2. Add the slowly changing dimension component.
Double-click this control to set it. We will find that this is similar to an installer. You 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.
 
Let's go directly to the next step and complete the configuration. We can see that the slowly changing dimension contains three branches and their corresponding components, 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 the three automatically generated components. "Insert target", which automatically generates an ole db target component and automatically maps fields, 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. They are designed to update data.

Script

Update [DBO]. [product_teste] Set [makeflag] = ?, [Name] =? Where [productnumber] =?

 
Run:
 

Database:
 

 

Iii. Summary
Two components are used to add, update, and delete a data stream (only the condition split component is available.
It seems complicated to use it. You need to configure it manually. However, it is flexible and you can control different operations as needed.
The second is automatic generation, which reduces the difficulty of use, but is not flexible.

 

 

Project Step1 --- 4 source code file: the version is SQL 2005. You must install the Excel application before running the code.

/Files/cocole/Step1-4Sql05.rar

 

Author: Wukong's Sky (tianma xingkong)
Source: http://www.cnblogs.com/cocole/
The copyright of this article is shared by the author and the blog Park. You are welcome to repost this article. However, you must retain this statement without the author's consent and provide a clear link to the original article on the article page. Otherwise, you will be held legally liable.