SQL Server BI step with step SSIS 4

This article supporting source code

The last time we did not implement the data in Excel and the data in the database to consolidate, there is an update, does not exist that is inserted. This is mainly about several methods to realize:

1. Using the Lookup

2. Use the Execute SQL task to invoke the stored procedure

3. Use script Component to implement

4. Use the merge statement (SQL SERVER 2008)

5. Use the last time we used the merge to achieve

6. Use third party component SCD Component

It seems that there are a lot of ways to do this, and we'll introduce and introduce the application of some components, and we can do the same with other functions.

Lookup

Create a new package mergedatalookup, we foreachinput in the copy over, we realize the traversal of Excel, while the data in Excel and the data in the database merge, in the data flow, under the Excel data source, delete the original components, Drag into the lookup component and select the settings for the lookup after OLE DB connection:

You can see that we are based on the ProductNumber field in the Excel data to find the corresponding data (column ProductID), that is, to find the corresponding data, ProductID will be added as a new column to our data flow, can not find the error, click on the The marked configuration error output,

So for the two output of lookup, the normal output is to find the productnumber corresponding data, at this time do the update operation. We have configured this in the figure above, and when a row is not found, we reset the row of data to the error output, and then insert the drill We add the OLE DB Command on the normal output to execute our UPDATE statement.