How can I use SSIS to map from an in place text value (in the source DB) to a FK table (in the dest DB)?

I have two databases. My source looks like this:

FirstTable
------
TestingData
-----------
   Test
  Values
    go
   here
    go
   Test

I want to map to a database that looks like this:

FirstTable
------
TestingDataId
----------------
       1
       2
       3
       4
   开发者_如何学运维    3
       4

TestingDataEnum
------
TestingDataId      TestingDataValue
-------------      ----------------
     1                 Test
     2                Values
     3                  go
     4                 here

TestingDataId in table TestingDataEnum is the primary key, but it is not an identity.

The problem is that I need a way to insert the values only when they are new and convert them to the numeric value (either the newly inserted one, or the existing one).

I can seem some possible ways to hack this together, but I can't think I am the first one to want to abstract repeated data like this. I would like to go with a proven solution rather than something that I just hack together.

Does anyone know of a good way to do this with SSIS?

Sounds like you need to use the Lookup transformation to find the string and insert that in to the data flow. You can also use Merge to achieve the same thing but have a read of this SSIS Junkie article: http://consultingblogs.emc.com/jamiethomson/archive/2005/10/21/2289.aspx

Alternatively, I'd recommend doing the lookup in the SQL providing the data (assuming the source data is coming from a database table as well).

EDIT: I missed the main point of your question with my initial answer, sorry, so I'll change it a bit. I thought you were only doing a lookup to change one data type to another but the same can be used to find out if a value is not in your table.

When the lookup fails you need to handle the error and perform the insert, you should then be able to use the new int value in your data stream.

Or is that the "hack" method you were thinking of? :)