We have a data warehouse with a few databases. Every morning we import data from source systems to our data warehouse. The final step used to be to initiate loading of an Essbase cube by a SQL job which remotely executes a bat-file on the Essbase server.
This has worked well for years. Now we added some complexity. We have started to import data from another source system. We have developed a new SSIS package which imports the very small files. The new data is not needed in the Essbase cube so we have scheduled the new sql server agent job to run at a time when the Essbase cube is still loading data from our fact tables.
The new sql server agent job starts as planned but does nothing. In job activity monitor the job can be seen as running. But nothing has been done in SSIS, we have extensive logging in the SSIS packages, but the log files has not even been touched. Then, 30 minutes later when the cube is fully loaded and the sql job managing it completes, our new job stops idling and finishes the import process within a few seconds.
It seems that sql server agent refuses to execute the SSIS package while the cube job is executing. The server is not choking under the workload. Manually running the SSIS-package while the cube is still loading works fine.
Any suggestions welcome...