Paul Hernández (5/3/2013)
Hi Folks,I have a package that loads records in a CRM destination using a Web Service. This execution takes around let´s say 3 hours. I would like to know what would happen if new records are inserted in the source table during the package execution?
Supposed that there`s no filter in the query that gets the source records.
I can program the loading process in another way, I know. But what I really want to know is what would happen in this scenario? Are the new records loaded? Maybe depends on the buffer, if all the records were already catch when the new records come in?
EDIT: "suppose that the OLEDB source that I'm using is still in Yellow when the new records arrive"
Any comment would be appreciated.
Regards,
The window is small after you issue the SELECT but it's possible for more rows to sneak into the resultset even when in READ COMMITTED mode.
When the source is yellow what is the wait type for the SPID selecting the rows? Async network or something else? Or is SSIS wrong and is the SPID cleared out?
This is probably what you meant by "I can program the loading process in another way, I know." but to remove most of the doubt I like to code extracts like this off of an ever-increasing key, i.e. an IDENTITY column setup as a unique index or primary key. This way I will select the maximum ID into a variable, then select all rows that are less than or equal to that ID.
Are you sweeping the rows, i.e. deleting them from the source after you push them through to the web service?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato