Getting ID after Insert from AutoIncrement column in MS Access

I am inserting new records stored in SQL Server into a legacy MS Access application using SSIS. During the transformation, I need to get the ID MS Access assigned to the autoincrement column in the MS Access table I am inserting the row into. Is this possible? Can someone give me an example?

Thanks,

Steve

Not possible in a batch type insert like SSIS does.

Why not make your own "AutoIncrement" column inside SSIS? http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/|||

Thanks Phil. I did not think so, but thought I would ask. I need the actual ID from the Access table so I can update the record in SQL server. The solutions is migrating to SQL server but in the meantime, information can be updated in either Access or a web interface to SQL Server. We have to sync the data between the two.

|||It may be possible if you insert one row at a time, but I'm not sure how to return the last AutoIncrement value in Access. In SQL Server it's @.@.identity, but not sure in Access.

You can still calculate your own autoincrement number. If nothing else is inserting into that Access table, you can turn auto-increment off. Then using the page I listed earlier, you calculate the max value which seeds the starting number for your upcoming inserts. Just a thought.|||

How can I force the transaction to complete within a dataflow? To solve this problem of retrieving the primary key assigned by Access, I added another column to the Access table to write the SQL key. When I add the record from SQL Server to Access, I write the SQL Server key to this column. The next step of the data flow is to re-read the record using the SQL key and retrieve the Access key assigned to the autonumber column. The problem seems to be that when I get to this step of the dataflow, the record hasn't actually been written so it does not complete the Lookup transformation.

Is there a way to force the transaction or do I need to move this step to a new Control Flow?

Thanks,

Steve

|||You'll have to move it to a new data flow. While the data flow does process "row by row", rows are processed in buffers. So one buffer (of ~10000 rows by default) has to be processed through the lookup before the same buffer can be written to the destination.