Records show up in the data table then then they disappear for a while
We are trying to implement the Attunity drivers and are having an issue with the record transfers. I query the target table during the SSIS process and the records are going into the target table. I watch the record count go up until all of the records are transferred. Then the record count drops to zero for a minute or two. Then the final count returns.
In the meantime, the next task in the process starts, creating a lock on the table. A that point it requires DBA to kill the process.
We would like to get these drivers working, our alternative is to use the OLE DB drivers in 32 bit mode, which is causing resource problems. Any ideas what could be happening?
As a follow up, could it be a buffering issue?
We are pushing data from SQL Server to Oracle data tables. We move all of the data into staging tables then issue merge commands to insert/update records as needed. Once the data flow is completed we run another data flow task that grabs all of the record counts for source, destination and staging tables used in the process and place the counts in a table, creating a daily log of activities.
What we find is that the numbers in the table of record counts doesn't always match the counts for the destination and staging tables when we query them later. The largest tables report less records than they actually have, as if records are still being written at the time we take the record counts.
Is there a way to force Oracle to complete a task before it starts on the next task given by SSIS?