I've been working on a particular project were we need to extract data out of two ERPs, transform it and then send it by secure FTP (OpenSSH).
I have evidence that make us believe that the 'profile' under which an especific task runs may vary between tasks within the same SSIS runs. I have an Script Task dumping an csv file into a secured folder and the next task is an 'SQL Task' which can't access it and therefore generates 'invalid file' error.
I have test everything using a public folder and works just fine, but when we use the restricted folder is when things get odd. I've been able to establish that in a particular 'Script Task' the owner of the Job plays an important role, because if the owner doesn't have access to a windows folder the task fails.
Later on I have established that another task fails if the Job runner doesn't have enough privileges on the box where is running.
Finally I haven't been able to walkaround the issue I already mentioned where a task creates the csv file and the next tryes to access it and it's denied.
All that whiout mentioning that where you run the package from Visual Studio is even harder to tell which profile is being use by the particular task.
Does anyone knows where to go?