Drove me crazy in the last few days - scenario:
Setup SSIS package to execute SQL command as OLE source (inside a workflow task) - on local or remove server;
Execute package manually (Debug) - all works fine - you get insert, update, delete all ok.
Set this package to run as SQL Agent - you get the agent running ok, no error reported - but only INSERT was executed - not update not delete - this is NOT a permission issue!
The solution - you MUST add a DESTINATION to your workflow task - otherwise the OLD source will simply not complete the task and will just assume that you do nothing.
So set a Script (empty one) as destination - drag it from the toolbox, it will open up a window as you whats its role - click destination, then connect it from the source with the arrow, then double click the script to build it and save, close and ok - thats it - you are good to go.
Thanks Microsoft for make it complicated - how about just an SQL task (without the trouble of source and destination)???
