1

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.

Amira Bedhiafi
  • 8,088
  • 6
  • 24
  • 60
Vlogs Bengali
  • 85
  • 1
  • 13
  • Are you running the package in 32bit mode? – Thom A Aug 20 '19 at 13:14
  • I am running the package in 64 bit via job, since ace.oledb. 16.0 64 bit is installed – Vlogs Bengali Aug 20 '19 at 13:54
  • Your prod box may be running out of desktop heap space. Check out the MSDN blog entry for more info: https://blogs.msdn.microsoft.com/dataaccesstechnologies/2014/05/21/ssis-package-fails-with-error-the-step-did-not-generate-any-output-the-return-value-was-unknown/ – digital.aaron Aug 20 '19 at 14:55
  • I have changed the shared value from 1024, 3072,768 to 1024, 3072,11536.Still it is not working. Any suggestion regarding the value I can try. I Have 5 excel files total size around 40mb. – Vlogs Bengali Aug 20 '19 at 16:30

0 Answers0