How to set foreign key in child table where key is from a target parent table
I have a working dts that has multiple CRM source entities with a parent child relationship between them. The dts is copying this data into my OLTP database into a parent and child staging table, mirroring what is in CRM for a workflow process in CRM. My problem is the key on the parent table in my OLTP database is generated on insert from the Scribe dts (it's identity column). I need to get that key and place it in the child table, so I know which rows were put into the staging tables that match the parent row at the same time. I have tried several things using Seek and a target variable, none of which have worked. It seems when I set the target variable from the seek, somehow the target value doesn't get set (I tried using SQLQUERY to get the key value from the parent table). I see examples where Scribe will do this for you, but only if you are getting the key from the source, which I am not. What am I missing? What is the proper way to do this and is there a step by step example of doing it somewhere?