I am creating a package to read data from our production iSeries (AS/400) and copying it into our new data warehouse. I am reading millions of rows at a time, and a couple of them will be updated while I am doing the read. The SSIS package is picking these records up twice, once before, and once after the update. I have tried changing the isolation level, but no luck.
Looking at footnote P2, it appears that this may be by design. I have looked for a READPAST hint or something similar, but I haven't found anything.
Has anyone else dealt with this issue? Is there a way to solve it short of reading all the records into a pre-staging table prior to loading the staging table?