This may be obvious, but I've been banging my head for a while... please point me in the direction of an article if there is one.

I'm importing a series of Excel files into a SQL database.

They will have a list of text items with a value:

Name   |   Value
Name1  |   100
Name2  |   140
Name3  |   200

The target database has a lookup table of "Name" mappings.

What I want to do is find a corresponding NameID for a row, and put that into a column (this is the easy part), however, if there is no corresponding entry in the Name table it should insert the row and get the new ID.

Is there an accepted way to do this? I'm looking at low volumes, so I don't need it to be particularly performant, just simple and easy to follow.

  • Assume Name3 does not exist in the reference/lookup table. Is it possible that Name3 would appear multiple times in the same Excel spreadsheet?
    – billinkc
    Mar 20, 2013 at 15:00
  • no, I would do a sort/dupe check. Mar 20, 2013 at 15:58

1 Answer 1


Option 1

It's been awhile since I have done it but basically this is what I did.

  1. After the lookup, do a conditional split.
  2. Split the NULL values off on their own path.
  3. Use an OLE DB Command to insert your rows. Be careful here because you don't want to insert the same row over and over again.
  4. Re-lookup your value (or get it back from the OLE DB Command if you can).
  5. Use a Merge to merge the two data flows back together again.

Option 2

Do it with 2 data flows. In the first one, find all of your data that doesn't match and do the insert. In your second one, do the lookup.

The latter option may be simpler for your purposes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.