October 15, 2025 at 1:18 pm
Good morning all,
I have been running into a very random weird issue that I am hoping someone has experienced and can stop my head from spinning. We have 4 environments we run about 150 flows that connect to Oracle to pull data into 4 different SQL Servers. Let's called them Dev1, Dev2, Test and Prod. Dev1 pulls from Oracle and loads to the Dev1 SQL server and so on. SQL Server 2019 btw.
This has been ongoing for a while now but we just recently noticed it but what would happen is a table in Oracle should bring back 191k rows and it brings in only loads 55350, 110701 or 166052. We noticed this tends to happen on days after we do not typically run. Our items are on a schedule and they do not run on Mondays or days after holidays so this happens typically on Tuesdays or a day like Today when Monday was a holiday. What we have confirmed is the data is available in Oracle before we begin running. This also happens randomly on 1 or more of the 4 servers. This happened today where we received 110701 rows but expected 191k only in Dev1, Dev2, Test and Prod all loaded the proper 191k. Also, the packages run at the same time on the servers. Not exactly the same time since there are 150 or so of them all using different queries to pull data but they all begin around the same time. The check we have is before we begin running, we use our source query and run a count of the records and show 191k. Then after the table loads, we query the table to see its records and shows the 110k in Dev1, where dev2, test and prod have 191k. Same exact code in all 4 environments.
I am not really looking for a way around this but what the culprit could be or what I can have a network team or DBA team look into like cpu, memory, etc. But what is interesting is the SSIS log is not showing any errors. It acts like everything is fine and just finished even though its short records.
We are using SSIS 2019 Data Tools 16.0.622205.05200 and Integration Services 15.0.2000.170 and using the Attunity drivers for Oracle. (we have not upgraded to a newer version of SSIS because we lose the Attunity drivers and our Oracle loads go from taking 20 minutes for about 150 flows to an hour or more).
I am not sure what else to share so please let me know what information I can give you and I will do my best.
Thank you in advance!
October 15, 2025 at 2:37 pm
Have to check the logic, but likely there is some filter (WHERE) looking for certain rows that is missing things. I've had similar issues when someone assumes a process is running on a certain schedule and it changes. Like every day and we skip a day.
This is one reason for loads/changes, I often want to store the "last run" datetime so that I use that as my filter and not an assumed interval.
October 15, 2025 at 2:49 pm
I wish it was that simple. lol. The logic is the same on all 4 servers. Literally identical and has been for the better part of 5 years.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply