SQL Server Bi step by step 4-2 Merge data lookup component and script component to complete data merging

Merge data 2
---- The lookup component and the script component Merge data.

 

This chapter describes how to integrate data in Excel with data in the database, including update and insertion:
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.

I. lookup component
Objective: To merge data in Excel and database while traversing multiple Excel sources, that is, there is data update and no data insertion.

 

 

 

 

 

 

 

 

In the second loop, you can test and update the data! Similarly.

 
 
2. Create a new package mergedatalookup.
3. Create a foreach loop container, put the data flow task in it, and edit it. Configure the cycle according to Step 3 batch import excel. Pay attention to configuring variables and modifying attributes of foreach !!!
 
4. Switch to the data flow tabpage and drag an excelsource to connect the product1.xls, lookup, ole db command, derived column, and ole db target components to the product table to store the "error" record and insert the record.

 
5. Set the lookup component.
Set the tab reference table:
 
Tab column settings:
Use the productnumber field in the Excel Data to search for the corresponding data in the database.(Number of records queriedEqualNumber of rows of the Excel Data source );
Select productid as the column displayed in the data stream after lookup query.
When the corresponding data cannot be found, click the configuration error output marked above.

 
Set "configuration error output"
 
If this parameter is not set, an error is returned:
----------------------------------------------- Error Response ---------------------------------------------------------------------------------------
Error: 0xc020901e, located in the data flow task, lookup [39]: No match is generated for the row during the lookup.
Error: 0xc0209029, located in the data flow task, search [39]: "component" Search "(39)" failed, error code: 0xc020901e, and for "output" Search output "(41) "Error row processing settings are specified. Once an error occurs, it fails. An error occurs on the specified object of the specified component.
Certificate --------------------------------------------------------------------------------------------------------------------------------------------

6. Add the update command to the ole db Command component to control the output.
 

 

 

Update production. Product Set [name] = ?, Makeflag =? Where productid =?

 

Set the ing between Update column fields and parameters. Be sure to map the lookupid parameter! How can we get the parameters? That's the parameter in your update statement.
 

7. Set the derived column component
 

8. Set ole db and ignore rroductid and rowguid. Name and productnumber are mapped to the new columns of the derived class.
 
After completing these eight steps, add the data stream viewer you are concerned about to run the package:
The first time, the execution is successful!
 
 

The second time, run.

 

 

In the database, when the database runs for the first time, two rows of records are inserted.

.
 

Careful friends may see that the values in the update output and error output after two lookup operations have not changed, and the database value has been inserted after the first execution. Under normal circumstances, the update output should be 4 rows, and the error output should be 0 rows! But there is no error in the entire operation!
Why? I guess the cache or SQL query view is used at this time, or ??, As a result, data is directly read without actually going to The lookup new database. What should we do? Problem!

 

Ii. 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. In the stored procedure, we only determine whether the data exists or not based on productnumber, if yes, update is executed, and no insert exists. this method is not described in detail here.

 

3. Use script component
1. Create a new package mergedatascript.
2. Add control flow and Excel source components.
3. Add the script component.
Select the script control to use -- convert
 
 

Set the input and output parameters. There is an input. The three outputs are named updaterecordsoutput, insertrecordsoutput, ignorerecordsoutput, and manually configured. Note that the ID of the input recordsinput is 115. Set the output attribute exclusiongroup to 1 and set the value of the attribute syncronousinputid to 115.. (Note: The syncronousinputid of each input control is different. setting these two attributes is the key to running the script below. For details, refer to the official documentation)

 

Edit the Connection Manager.
To obtain the database connection from the script, you need sqldatareader to create an ADO. net connection.
Create an ADO. net connection

 

Connect the script Connection Manager to the new ADO. net
 

 

Edit script

Script

Imports system
Imports system. Data
Imports system. Math
Imports Microsoft. sqlserver. DTS. pipeline. wrapper
Imports Microsoft. sqlserver. DTS. runtime. wrapper

Imports Microsoft. sqlserver. DTS. Runtime
Imports system. Data. sqlclient

Public class scriptmain
Inherits usercomponent

Dim connmgr as idtsconnectionmanager90

Dim sqlconn as sqlconnection

Dim sqlcmd as sqlcommand

Dim sqlparam as sqlparameter

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 acquireconnections (byval transaction as object)

Connmgr = me. Connections. dbconnection

Sqlconn = ctype (connmgr. acquireconnection (nothing), sqlconnection)

End sub

Public overrides sub input 0_processinputrow (byval row as input 0 buffer)
Dim reader as sqldatareader
Sqlcmd. parameters ("@ productnumber"). value = row. productnumber

Reader = sqlcmd. executereader ()
If reader. Read () then
'Here we 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 sub

End 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 implemented using lookup. use productnumber to search for the name field. If the name field is found, the system jumps to the update output. Otherwise, the system jumps to the ignore output field. If the name field is not found, the system jumps to the Add output field. 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.

 

4. Three outputs are set in scriptcomponent. updaterecordsoutput corresponds to the OLE dbcommand component. insertrecordsoutput corresponds to the ole db target component and ignorerecordsoutput corresponds to the row count component.

 

5. Set the OLE dbcommand component.

• Set the Connection Manager to the original ole db Manager
• SET command
  

  Update production. Product Set [name] =? Where productnumber =?

• Column ing

6. Set the ole db target component, which corresponds to the original ole db manager and points to the product table.
(Updaterecordsoutput, insertrecordsoutput is the same as lookup)

7. Set the row count component.

• Add a variable. The scope is the data flow task.

In the properties of the row counting component, find the variable variablename set to the variable -- User: rowinore.
 

Run: My problem is that the output is displayed successfully, but the data stream does not have data.

 

 

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.