I'm looking for some ideas. Currently I have 3 SSIS packages which are scheduled in SQL Server Agent. The run every 10 mins, and updates the DW. The first step in all 3 of them is to check if the latest flat file is in a directory, if not quit, else load the data into the DW, easy.
All 3 packages runs every 10 mins, first 1 on every 10 th min, second on the 11th min and 3 on the 12th min.
The packages can execute for an empty file in a few seconds, for daily data a few mins, and at the start of the month even a few hours. All 3 packages runs in parallel.
The requirement now is for me to provide data to a validation system which will be used when the mainframe is down. The table which I’ll populate is on a different server, but the table is self will require data from all 3 tables which is updated via the 3 packages.
So, therefore I need to make sure of the following:
1) That the 3 packages have finished successfully
2) That there is new data to be inserted to the validation table.
What would be best way be of achieve this?