January 23, 2009 at 1:07 pm
Hopefully, I can explain this well enough to be understood. I have had difficulty in finding others who are struggling in a similar situation simply because I think it might be hard to describe... so, here goes:
Within a Control Flow's Data Flow Task, data is extracted from SQL Server 2005 (utilizing a Lookup that joins the Source and Destination tables for reference) and then inserted into an Access 97 database. When each new row is created in Access, the Primary Key with Autonumber naturally generates a new ID. My problem is retrieving this newly generated ID for use in another Control Flow down the line that will insert it into another table in the Access database as a Foreign Key. I'm assuming I would retrieve this information into a variable for use; however my two problems right now are:
1. How do I get the ID into a variable from the destination table after an insert?
2. How do I know the variable is holding the correct ID that will be referenced down the line to be inserted as a Foreign key in a child table for every row that is processed?
Any thoughts are appreciated. 🙂
Of course, I waited until the last minute to ask for help, so I'm in a time crunch. :w00t:
January 23, 2009 at 1:52 pm
The only way I've been able to do this is create a Surrogate key in the destination table, and include it in the Data extract.
The Surrogate key from the extract can be the key from your source db, or a generated one.
and then you can include the parent surrogate ID in your child query join to parent to get native db's ID.
January 23, 2009 at 5:35 pm
I'm not quite sure what you mean, RM; and I'm also not quite sure how to ask you to explain further. Hmmm... how could the Surrogate Key be guaranteed to be the same in both the Parent and Child table?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply