I've been banging my head against a brick wall for what seems like forever. This is what I have done :
Written a VB.Net console application (lets call it myvb.exe) which accepts a number of parameters and creates an Excel 2010 workbook
Created an SSIS package which iterates a number of records in SQL Server and executes myvb.exe
This package works successfully when I run from BIDS. It works successfully when I publish the package into SSIS. It works successfully when I run from a command prompt using "DTExec.exe". What it doesn't do, is run as a scheduled job using SQL Server Agent. Well, strictly speaking, it does run, I get no errors, but the expected Excel files do not get created. Task manager shows me that Excel instances are being created/destroyed on the fly.
The agent is running under my logged on account, so there should be no issue there (hopefully). It is running on the same machine from which I designed all the elements, so there is no issue with a different setup. It doesn't use UNC path, the application is run directly from the c:\ drive. It is set to run using 32-bit runtime, hopefully that eliminates an issue we've come across with Office on the production server. I've tried turning on package logging, but it tells me little else other than when each iteration starts and finishes. I've added scripts tasks to write what is passed to the exe to a log file, all commands look OK.
I'm at a loss. I'm obviously missing something very basic, but I'm b***** sure I can't see it. Any guidance would be appreciated.
Thanks in advance.