Inserting a new lookup value for the SSIS Lookup Transformation

Question

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.

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.