locked
Importing Data that doesn't already exist? RRS feed

  • Question

  • I decided to make a project for myself so that I can expand my knowledge on SSIS and SSAS since BI is my kryptonite.  I am working on a package to load data from flat files into a database.  For this example I am using Email Addresses, and I need to do a Lookup to see if the email address exists to get the ID for it, and if it doesn't exist, I need to insert it, and get the ID.  I have figured out the lookup, but not the insert if it doesn't exist part.  I know I how to do this with TSQL, but is there a better way to do this in bulk with SSIS?

    Thursday, August 7, 2008 11:08 PM

Answers

  • You can use an OLE DB Command transform to do the insert and get the key back. Use the error output from the lookup transform to get the rows that didn't have a match, connect that to the OLE DB Command, and then a Union All to merge those rows back into the main flow.

     

    Friday, August 8, 2008 2:15 AM
  • Jonathan,

     

    review this link (from FAQ thread in first page), it has relevant information on how to configure the lkp transformation:

     

    Checking to see if a record exists, if so update, else insert

     

     

    Friday, August 8, 2008 2:43 AM

All replies

  • You can use an OLE DB Command transform to do the insert and get the key back. Use the error output from the lookup transform to get the rows that didn't have a match, connect that to the OLE DB Command, and then a Union All to merge those rows back into the main flow.

     

    Friday, August 8, 2008 2:15 AM
  • Jonathan,

     

    review this link (from FAQ thread in first page), it has relevant information on how to configure the lkp transformation:

     

    Checking to see if a record exists, if so update, else insert

     

     

    Friday, August 8, 2008 2:43 AM
  • Thank you both for the information.  I am sure I will have other problems in the coming days that I'll be asking about. I really appreciate the assistance.

    Monday, August 11, 2008 1:28 PM