Retrieve data from Data Flow Destination after task executes

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » Retrieve data from Data Flow Destination...

Retrieve data from Data Flow Destination after task executes

Rate Topic

Display Mode

Topic Options

Author

Message

sql1138

Posted Friday, January 23, 2009 1:07 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 17, 2012 12:17 PM
Points: 5, Visits: 46

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

Post #642724

Ray M

Posted Friday, January 23, 2009 1:52 PM

UDP Broadcaster

Group: General Forum Members
Last Login: 2 days ago @ 8:25 AM
Points: 1,467, Visits: 922

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.

Post #642763

sql1138

Posted Friday, January 23, 2009 5:35 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 17, 2012 12:17 PM
Points: 5, Visits: 46

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?

Post #642853

« Prev Topic | Next Topic »

Permissions