Correct, will need the sync every hour.
Hi Jeff, yes, I do have control over the SSIS package and it has over 100+ Data Flow Tasks. Big. Most are repeated table processes. A generic task would be:
Source Table (OLE)
Delete Old Rows
Destination Table (OLE)
The package that they provide to us has two issues. First, some fields in the package do not match the fields in the tables, including data type. This has caused some issues. It seems that they have added/deleted fields in the database but did not update the package on a quarterly release. This is something that not much can be done here except like you said, I have control over it. Second, I was able to get the package to run and it shows successful but when compare the total records between tables, they are not the same number. Scary thing here is the package ran fine but we are missing records. In some cases, large amount but I wonder if the "conditional split" has something to do with this? You asked about a filter. No, I do not see a filter. The only where clause that I see is it updates based on the latest (differences).
"with (NOLOCK) where modifiedutc > ? and modifiedutc <= ?"
the first parameter is "Last Modified" and the second parameter is the "Most Recent".
If this is supposed to be a 1-1 sync, why would it need a "Conditional Split"?