I have a SSIS package with 6 DFT's .Each DFT having connection to EXCEL (connection string -Provider=Microsoft.ACE.OLEDB.16.0;Data Source=ABC:\xyx.xlsx;Extended Properties="EXCEL 12.0 XML;HDR=YES";) as source and sql server as destination.I have ACE.oledb.16.0 driver installed in both my dev box and prod box.
When I am running the package from SSDT it runs with success.
But when I am trying to run it via job I am receiving a strange phenomenon.
The job fails after executing 2/3 DFT's successfully(yes sometimes the 3rd dft does not run and the last 3 DFT does not execute) and the job fails with error
Message Executed as user: user1. The step did not generate any output. The return value was unknown. The process exit code was -529697949. The step failed.
When I disable the top 3 tasks and run via job the package run successfully executing the last 3 dft.
For some reason all the 6 DFT's are not executing in one run.It is very hard to debug with the error message provided above can you help me with any inputs.
This behavior is not because of any coding part.it runs absolutely fine via package SSDT.