SSIS Question

[jibarra]

Good Morning. I am New to SSIS. I have a task of getting an existing SQL 2008 Database and moving the data into a new Database with new design. Some of the tables will now have new fields. Some of those fields will have their own identity Keys. For each record I insert in a new table, I will have to get that Identity key and use it for other insertions in different tables. Can I do this with SSIS, if so is there an example solution somewhere ?

Thank you.

 

[mrdenny]

You can, but it'll be complex to do. Somewhere you'll need to log the old IDs and the new IDs into a table, so that you can use a merge task or a lookup to get the new IDs. It is much easier to simply keep the old ID values, and set the new system to start with a value much higher than the old system ends at.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog