SSIS 2008 Flat File Source Deployment

Question

I'm having a rather frustrating issue with using an SSIS Flat File source. I am developing an SSIS package on my local machine via VS 2008 and I'm using a flat file source that is stored locally. However, I need to deploy this package to a remote server that hosts our SQLServer and then run it as a scheduled job from that host. However, when I deploy the package, it obviously can't read the flat file source from my machine and fails the job. I have tried putting the file directly on the remote host in the exact same file location (ie. C:\Source.txt) but to no avail. Any ideas on how I can get my deployed SSIS package to read a flat file source?

Hopefully I'm just missing something extremely simple and will ultimately have a "DUH" moment, but if anyone can help I will greatly appreciate it.

Answer 1

Nice answer by Cade.

Remember that you create a SQL Server Job, by default it runs under the credentials of the Service Account assigned to the SQL Server Agent.

If some steps on a Job need some permissions not owned by the Service account you can define a SQL Server Agent Proxy.

That way you can keep the principle of least privileges.

More info on how to create a Proxy here.

Answer 2

If the account under which the SSIS package (for instance the SQL Server Agent service account) is running doesn't have rights to open the file, you will also have problems. So not only does the file need to be on a path that is valid relative to the server running the package, it must also have rights.