compare and insert

• patdev00

  SSC Enthusiast

  Points: 174

  More actions

  March 16, 2022 at 5:24 pm

  #4000732

  Hello,

  new to ssis so wanted to find out

  how to compare the data in the destination then get data from source and insert what exist (in source)

  for example

  i have set of record id in the destination.

  based on that i need to bring that record id only from source to destination.

  Thanks

  Pat

   

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736172

  More actions

  March 16, 2022 at 5:42 pm

  #4000776

  This isn't quite clear what you want as a scenario. If you want to examine records in a destination and use that in your flow, there are tasks for that. There are a number of articles here on how you can control flows: https://www.sqlservercentral.com/stairways/stairway-to-integration-services

• SoCal_DBD

  Hall of Fame

  Points: 3380

  More actions

  March 23, 2022 at 5:41 pm

  #4004890

  I just had to do this for the first time myself and had a little trouble but got it working this morning.  Here's a high level:

  I'm assuming you have a situation where, as a simple example, source has 10 records, destination has 7.  You need to insert the 3 records from the source that do not exist in the destination.

  • Use a Data Flow Task
  • Edit the Data Flow Task
  • Add an OLE DB Source connection for your source server and configure accordingly (select the connection manager and the table)
  • Add a Lookup task.  Connect your OLE DB Source connection to the Lookup task.  Edit the Lookup task and on the "General" tab, select "Redirect rows with no match to output" in the drop down.  On the "Connection" tab, select your destination server and corresponding table you need to insert into.  On the "Columns" tab, click and drag the PK from the Available Input Columns to the corresponding PK in the Available Lookup Columns.  This is what it will use to determine if there are new records. (In my task, I did not check any of the checkboxes in the Available Lookup Columns.)
  • Add an OLE DB Destination connection for your destination server.  Connect the Lookup task to the OLE DB Destination.  In the "Input Output Selection" popup, select "Lookup no Match Output" in the "Output" drop down.
  • Edit the OLE DB Destination and configure for your destination server and table.  Make sure to select the Mappings tab to get the columns to map.

  I'm definitely no expert when it comes to SSIS, but was able to get this working.  If I still ran into  trouble, I was going to go to that Stairway series Steve referenced. 🙂

  Good luck.

  Attachments:

  You must be logged in to view attached files.