March 3, 2009 at 9:21 am
I have 3 tables where Table A is a reference table holding widget types. Table C holds Widget Orders and has an order number. Table B is a junction table with a many to many relationship with the primary key values from Table A and Table C. How would go about performing an insert into Table B after loading Table C and having newly created Table C primary Keys?
March 3, 2009 at 9:33 am
Use 2 separate data flow tasks, one to load the primary key table and the other to load the M2M table with the foreign key. Use your precedence constraint to insure that the primary key data flow occurs before loading the foreign key table.
You could also use a lookup when loading the foreign key table to confirm that the primary key value already exists, and send the unmatched rows, if any, to another output for review or reprocessing.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
March 3, 2009 at 9:37 am
How would I access the new foriegn key from the first load? Sorry if this is a newby question kinda new to SSIS.
March 3, 2009 at 9:42 am
Is your foreign key value a part of the data flow already? If so, there is no need to retrieve it from the primary key table - simply load the data into your Many-to-many table. I would suggest using the Lookup Transform to confirm that the primary key value already exists.
If you are creating a surrogate key when you load the primary key (such as using an Identity column), you would use the Lookup Transform to confirm that the primary key value already exists, and you can pull in the surrogate key value using the same Lookup component.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
March 3, 2009 at 10:41 am
I'll state it another way. I will be inserting a record into a table and that table will create a new row guid. how do I access the new row guid?
March 3, 2009 at 11:03 am
That's where you'll use the Lookup Transform component to look up the GUID value for each row that is headed for your many-to-many table. Do the lookup so that you match on the existing key value (not the new GUID). You can retrieve the GUID value by checking the box beside it in the settings of the Lookup Transform. This will force the Lookup to also grab that value when it does the lookup validation, and will add it as a new column in your data flow. You can then use that GUID value as your foreign key.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
March 3, 2009 at 12:05 pm
I appreciate your help but the problem I have is that I won't have an existing key value that I can match them on. I do but that value is reused with each batch I import beginning with 1,2,3.... I guess I could add another field in the database to hold that value then set all of that columns value to null after the prcess completes but that would add overhead. Is there no way to access the last Row Guid created?
March 3, 2009 at 2:05 pm
I wouldn't recommend trying to access the last GUID entered, since they are nonsequential by nature. Can you post a sample of the source data?
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply