Return The Generated Primary Key

Dear all,

I would like to use SSIS to populate product information from a remote database to a local one.

There are Product table (which contains the basic information) and ProductImage table(which contains the product images in binary).

I would like to use a join query

SELECT A.name, A.price, B.image from Product A INNER JOIN ProductImage B WHERE A.ProductImageID = B.ProductImageID

to return all the product information from the remote database.

When populating the local database, the local ProductImage table will be populated first and then the local Product table will be populated with the ProductName, ProductPrice return from the query together with the newly genetated ProductImageID afer insert a record into the local productimage table.

The ProductImageID column is set as the identity column in the ProductImage table.

I am very new to SSIS. Is there a way to handle this scenario? What components do I need to use?

Thanks