SSIS 2008 - Getting the identity field

I have to read in a text file that has a header row and some details with SSIS. I have two tables (one for the header and one开发者_如何学运维 for the details) that I need to get the data into. I need to insert the header and get the identity for that row, so I can insert that value into the detail rows. How can I accomplish this?

Currently, I'm reading in the header row and putting it into the database with an OLE DB Destination task.

You have three options.

First, write all data to destination. Then read all data from file again, use Merge Join component to match rows with the ones you wrote in database, get identity column, insert into detail table. Drawback is that You have to read the same file two times.

Second, read all data and write to both header and detail tables, but your destination header table should have one additional column (identity). Then issue one update statement to update FK from detail table to header table (identity).

Third, in case You are fine with writing row by row into database, You can make a loop over source data and in that loop: write header data, do execute sql task to read last identity, derived column to update identity column in pipeline, write detail data. This solution is very slow. (this way, watch for getting identity columns correctly, or use sp to insert data so that sp ccan return identity for You to eliminate mentioned execute task)