i am trying to figure out how i can create an SSIS package to insert into multiple tables. After the first insert, I want to take the ID created (an Identity column) and then use that to insert into other associated (foreign key) tables.
For example, I have a table Products. The primary key is an Identity column. Once the SSIS insert is complete, the bulk load of new Product has an identity ID value for each row. What I want to do, during the same SSIS package, is to take each row as it is inserted and add rows to other tables. Like, ProductsImages - it has a foreign key for the Product id and a foreign key to the ProductsImages being added. And, as part of this I will need to get the latest ID value and possibly some other values and store them in variables.
Thanks