I have a table in one DB that must be converted to 2 tables in another DB. Some of the columns must be cleaned up with an addition of some new columns so will need to use a Derived Column Transform. The second table, Table2, has a foreign key which should be the primary key (identity) in Table1 to keep some linkage information for each row after the conversion process. How would you implement this to get the identity value for the inserts in Table1 back so you can insert the foreign key in Table2 during the row transform. Would the process in the designer look something like this? Ole DB Source | Execute SQL Query - get rowset from Table1 | Foreach loop - iterate row by row Derived Column Transform Execute SQL Query - for inserting transformed row into Table1 - somehow get the key back here and then do another insert? | Ole DB Destination What would the flow look like in the designer? Thanks for any help with this!

It is better to import two tables separately. I mean : do not use Foreach , just use Two data flow task one after another. in first data flow task , import the table which contains PK which you want to use it in another table as FK. you can import this table with no problem. this can be good practice if you put Source Table ID in a new field in destination table for further lookups. in second data flow task, you can fetch second table and use Lookup transform to look for new IDENTITY values in the table which you filled in previous data flow task, you can use SourceTableID field for joining key. Does it make sense to you?http://www.rad.pasfu.com

Is it possible to do it without the new field in the destination table? I'd rather not have extra fields if they don't have to be. Also, if the SourceTableID field is a just a key to the source table it won't link to each record later will it? I am not sure I understand that flow? What would the SourceTableIDs look like? Also I've seen some other responses to this type of issue was to use a Row Number transform to use as 'temporary' keys which can be used later in the other table as the foreign key before it does the 'commit'. Would this be something that could be used instead? Use a Data Flow Transform for Table1 and use the Row Number transform for the IDs. In second DFT use a Lookup on the Row Number transform from the first DFT?

OK, if you want to do it without adding an extra column in destination, yes, you can, and the solution you are working already is one of the ways to implement it. there seems be other ways such as doing all operation in data flow without foreach loop, but as you worked with first solution continue your way there. in the execute sql task inside foreach loop, after doing insert you can get result into a variable, and then in next data flow task, you can add this variable value with derived column transformation into the data stream and write it to destination table. does it make sense to you? if not, give us more explanation about your package and what you did till now and we can help you better in details.http://www.rad.pasfu.com

Perhaps this article could help: Inserting Records AND Getting the Identity in SSIS. Talk to me now on