I recently had an issue when implementing an integration for a customer, where I would get a generic Error when trying to run my SSIS package from my SQL job agent, but everything ran fine, when I triggered it in Visual Studio.
I was using an OLEDB connection to an SQL stage table, where I want to fetch an Error string, which I then would like to print into my CRM organization. Easy enough. The Errortext is a WSTR,1048. My field in CRM in an NTEXT field, so my first thought, was to use a Derived column to convert the string to NTEXT: (DT_NTEXT)ErrorText
When I ran this job in Visual Studio to test it, everything was fine and Smooth, but as soon as I tried to trigger it from the SQL job agent, it threw an error. Specifically, the error wrote: Executed as user: ******. The step did not generate any output. The package execution failed. The step failed.
This can be very frustrating, when the error doesnt really give any hints as to what caused the error – and Visual Studio doesnt throw any errors. After some Trial and error, I narrowed it down to my typecast of the Error text. I then tried casting the errortext as DT_TEXT, but with the same result – no error in VS and no output in SQL job agent.
My solution to this issue, was to use a Data Conversion in SSIS, where I converted the string to DT_TEXT.
This issue caused me more problems than it should have, since it was part of a rather large SSIS package, and the Trial & Error was a tedious process – but atleast it worked out.