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

Last time, we did not integrate the data in Excel with the data in the database. If there is an update, there is no insert. This time we mainly introduce several methods to achieve this:
1. Use Lookup
2. Use Execute SQL task to call the Stored Procedure
3. Use the script component script to implement
4. Use the merge Statement (SQL Server 2008)
5. Use the merge we used last time to implement
6. Use a third-party component SCD component

It seems that there are indeed many implementation methods. We will introduce them one by one and introduce some component applications. We can also use them when implementing other functions.

1. Lookup

Create a new package, mergedatalookup. Copy the package in foreachinput to traverse the Excel file and merge the data in the Excel file with the data in the database. In the data stream, under the Excel data source, delete the original component, drag the lookup component, select the ole db connection, and set the search:

We can see that the corresponding data (column productid) is searched in the database based on the productnumber field in the Excel data, that is, when the corresponding data is found, productid will be added to our data stream as a new column. If it cannot be found, an error will occur. Click the configuration error output shown above,

In this way, for the two outputs of lookup, the normal output is that the data corresponding to productnumber is found, and the update operation is performed at this time. we have configured. When a row is not found, we will reset the row of data to the error output, and then insert the row. we add ole db command on the normal output to execute our update statement.

Add the previously added ole db destination to the error output. The productid and rowguid fields are ignored. The entire data stream is as follows:

Now we have achieved the use of lookup to merge data.

2. Use stored procedures
It is much easier to use stored procedures, but we do not recommend that you do this. We put all the processes in the stored procedures instead of the SSIS package, one thing to consider is that we can generally set it to support transactions in the SSIS package (set the transactionoption attribute of the package or component ). in the stored procedure, we can directly adopt the transaction mechanism in the stored procedure.
We create a new package, mergedataprocedure, to complete the settings of the preceding package, we only need to execute an ole db command. Here we call the stored procedure, during the storage process, we only judge whether the data exists or not based on productnumber. If the data exists, update is executed and no insert is performed. this method is not described in detail here.

3. Use script component

Create a new package mergedatasup, copy the control flow and variables in mergedatalookup, connector, and replace the loopup component with the script component (the conversion method is used when adding the package ). first, select the input column and set its usage type:

Then set the input and output. Here, there is an input, and then set three outputs. The output columns do not need to be manually configured and are automatically created. Note that the input recordsinput ID is 2778. the three output names are updaterecordsoutput, insertrecordsoutput, ignorerecordsoutput, set the output attribute exclusiongroup to 1, and set the attribute syncronousinputid to recordsinput (ID 2778 ), the syncronousinputid of each input control is different. setting these two attributes is the key to running the script below. For more information, see the official documentation.

Finally, set the Connection Manager. Because we need to obtain the database connection in the script, we will add a connection name here. Note that I have not connected to the original OLE DB connection here, I used sqldatareader in the script, and a new ADO is required here. net connection.

After setting, switch to the script, directly set the script, open the Script Editor, enter the following script, and close. OK.

Imports systemimports system. dataimports system. mathimports Microsoft. sqlserver. DTS. pipeline. wrapperimports Microsoft. sqlserver. DTS. runtime. wrapperimports Microsoft. sqlserver. DTS. runtimeimports system. data. sqlclientpublic class scriptmain inherits usercomponent dim connmgr as limit dim sqlconn as sqlconnection dim sqlcmd as sqlcommand dim sqlparam as sqlparameter public overrides sub acquireconnections (byval transaction as object) connmgr = me. connections. dbconnection sqlconn = ctype (connmgr. acquireconnection (nothing), sqlconnection) end sub public overrides sub preexecute () sqlcmd = new sqlcommand ("select [name] from product where productnumber = @ productnumber", sqlconn) sqlparam = new sqlparameter ("@ productnumber", sqldbtype. nvarchar, 25) sqlcmd. parameters. add (sqlparam) end sub public overrides sub recordsinput_processinputrow (byval row as recordsinputbuffer) dim reader as sqldatareader sqlcmd. parameters ("@ productnumber "). value = row. productnumber reader = sqlcmd. executereader () If reader. read () then' you can compare fields as needed
If (Reader ("name "). tostring () <> row. name) then row. directrowtoupdaterecordsoutput () else row. directrowtoignorerecordsoutput () end if else row. directrowtoinsertrecordsoutput () end if reader. close () end sub public overrides sub releaseconnections () connmgr. releaseconnection (sqlconn) end subend class

The specific meaning of the above script will not be described in detail, which is easier to understand. In fact, it is the same as the function we use lookup to implement. We use productnumber to search for the name field. If we find the name, we will jump to the update output, otherwise, the page jumps to ignore output. If no output is found, the page jumps to add output. we can also directly add and update these operations in the script. however, to make the entire process clearer, we only use the script to perform a conversion. but in fact, the implementation of the script will be more flexible. In fact, two-way search or more complex functions can be implemented here.

We add the corresponding output after the script component, where updaterecordsoutput and insertrecordsoutput are the same as lookup. However, we add a rowcount for the ignorerecordsoutput output for statistics.

The execution package completes data update and addition.

 

Well, it's a bit difficult. Today we will only introduce the implementation of these three methods. Next we will briefly introduce the other three methods.

References:

Http://www.mathgv.com/sql2005docs/SSISTransformScriptETL.htm

Http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

Http://www.sqlis.com/post/Get-all-from-Table-A-that-isnt-in-Table-B.aspx

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 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.