Tuesday, September 22, 2015

Using OLE DB Command to insert new records & update Existing Records in SQL SERVER Database Table

The OLE DB Command transformation runs an SQL statement for each row in a data flow. It is used to execute an SQL statement that inserts, updates, or deletes rows in a database table.

Package Requirement:For a given source file if the record exists in the database table,update for the new values.If not then insert the new records

Source File:
Lookup/Destination Table:
The package should insert records 4,5 and update record 3 for the new emp_location

Package Design:

Configuring the text file source..


 Data Conversion Transformation converts the source datatype to the column data type in lookup reference table




Lookup Transformation:





For OLE DB Command for matching rows(update)



Map the parameters to the fields in the source file




For OLE DB Command for non matching rows(insert)


Map the parameters to the source file columns.
 

After Package Execution:



Posted by at

4 comments:

  1. M@June 3, 2016 at 10:16 AM

    you totally just saved me hours :)

    ReplyDelete
  2. James ZicrovMarch 22, 2019 at 5:06 AM

    This comment has been removed by the author.

    ReplyDelete
  3. James ZicrovApril 3, 2019 at 4:50 AM

    A big thanks to you,I was completely unaware of the OLE DB command and its usage with SSIS Upsert because I am a beginner to SQL but this post was like a sea of knowledge for me and helped me to grab everything.Thanks again.

    ReplyDelete
  4. AnonymousJanuary 29, 2020 at 1:52 PM

    can you update the insert sql query for oldb command with where clauses.

    ReplyDelete

Subscribe to: Post Comments (Atom)