SQL Server BI step with step SSIS 4

This article supporting source code

We have introduced three methods for merging data stream source and database data. That is, the data in Excel and the data in the database to synchronize, the existence of the data to update operations, do not exist to add data, but also can be extended to, if the database for the data in the Excel data source does not exist, The delete operation is performed. Next we will introduce other methods to achieve this goal.

As we've described in merging data 1, it's not straightforward to implement directly through the merge and merge Jone, but we can do it in a workaround. First we create a new package mergedatamerge, like before, prepare the control flow, variables, and connection Manager as before. In fact, as with the MergeData2 in merge data 1, the Excel data source and database table data are merged according to the ProductNumber field.

Here we choose a full external connection so that we can completely merge the data on both sides. When one party data does not have a matching data, there is null instead. So we directly according to the merged ProductNumber and Newproductnumber can directly this line of data should correspond is added, Update, or delete. Under the merge Join component, we add the conditional split component, which is the component that determines the branching for different situations:

Quite simply, when the corresponding productnumber in the row is null, the matching data is not found from the database, so this data should be added, and when Newproductnumber is null, Explains that there is no productnumber data in the database in the Excel data source, so this data should be deleted, and finally, if two productnumber are the same, you should update. Here's what you need to be aware of, Be sure to put the two judgment isnull on the front, because we used trim in the third, and if there is a null value, an error occurs.