Update Destination Table @ID int, @Firstname nvarchar(50), @Lastname nvarchar(50)ASBEGIN SET NOCOUNT ON; update Upsert Destination Table set [email protected], [email protected] where [email protected] then add an OLEDB Command transform to the data flow task, and connect the Match Output from lookup transform to the OLEDB Command, go to OLEDB Command Editor, in connection managers tab, set the connection manager to destination database, in component properties tab, go to sql statement property, and write this update statement there: Note that question marks are parameter markers, and each of them represent a parameter, then you can map input columns to these parameters in the column mappings tab, That's all.with first run of package, the source rows will transfer to the destination, all 3 rows will go to no match output ; result in destination table is : now change a record in source, and also another record like this: and with second run of package, old row will go for update, and new row will go to insert and this is the result in destination db But suppose my sourse is like: id, firstname, lastname 5, Reza, Raad 4, Lary, Martin 5, Reza, Rad The lookup redirect both the two record with id=5 to insert determining a duplicate key error!which mostly used Lookup Transform with OLEDB Command, like this: But when you work with My SQL there is a limitation, you can not use OLEDB Connections, you can use ODBC or ADO. NET to My SQL Database, as described briefly here: How you can UPSERT with My SQL Tables?You will face problem with Lookup transform because you have no OLEDB Connection to mysql, and you can not use OLEDB Command on the other side.First Of All, create sample source file, this is our sample source flat file: then create a table with this structure in destination database: now go to SSIS package, add a data flow task, and add a flat file source, point it to the source file, and set Column names in the first data row, also go to advanced tab, and change the data type of column id to DT_I4.then add a lookup transformation, and in the lookup transformation editor,in general tab set the specify how to handle rows with no matching entries as "Redirect Rows to no match output" , then go to connection tab, and create a connection to destination database, and set Upsert Destination Table as lookup table.I am very appreciated if someone give me some hints. What made you think the name of the column was "Password" as opposed to "Pwd"? You didn't say what SQL Provider you are using, but the column referenced in an UPDATE statement SET command MUST be EXACTLY the same as the name of the column in the Providers "back-end".What made you think the name of the column was "Password" as opposed to "Pwd"? Perhaps he meant he changed the database column to "Pwd" as well as the query?
There are lots of links which described how to UPSERT( Update / Insert ) between two SQL Server tables.
Open() Dim my Comm As Ole Db Command = New Ole Db Command("UPDATE Orders SET Ship Name = @Ship Name, Ship City = @Ship City, Ship Country = @Ship Country, [email protected] WHERE Order ID = @Order ID", my Conn) my Comm.
Close() End Sub Sub Insert Record(By Val sender As Object, By Val e As Grid Record Event Args) Dim my Conn As Ole Db Connection = New Ole Db Connection("Provider=Microsoft.
then go to columns tab, drag and drop the ID column from available inputs to available lookup columns, Now you can implement INSERT part of operation, as below: add an oledb destination, and connect No Match Output from lookup transform to this destination, set the Upsert Destination Table there, and map columns.
For implementing the Update Part; first create an update stored procedure in destination database, as below: create PROCEDURE dbo.