locating a flat file from SQL server 2005

Question

I have built an SSIS job in visual studio 2005 that accesses a flat file and updates a table in SQL Server 2005. This works perfectly. I then store a copy on SQL Server by using the server storage option in the protection level property. When I try to run this it cannot locate the flat file. The flat file is located on a server and I use the IP address to access it. I have noticed that if I remove my maping to this server and run the job from visual studio, it cannot find it either. Hoe do I access the file from SQL Server 2005 ? I intend to schedule this job to run automatically on SQL Server. Many Thanks for any help you can give. Ronnie Carlin

Answer 1

Just to avoid anything more complex being the issue, check what account the job is being run under (e.g. has a procy and credential) or whether it is using the base SQL Agent account, and then check whether that account has access rights to the folder \ file you are trying to import. Had that catch us out more than once.

Edited to include the additional information in the main answer.

When SQL Agent runs a job it does not use your account, even if you initiated the job execution - it uses the SQL Agent account. The account the SQl Agent is set up under (by default) or a named account you specidy with a proxy / credential. You might be able to access the folder, but you need to check what account SQL Agent is running under and whether it can access it.

To find out which account the job is running under by default you can access it via Start -> Program files ->SQL Server -> Configuration Tools -> SQL Server Configuration Manager. In the 2005 Services SQL Server Agent will be listed, right click properties, first tab shows what account the Agent is being run under.

However, if you need a job running under a specific account I would advise you create a proxy / credential and not alter the agent account - since that is the account all the other jobs are using.

Credential and proxy are different objects within SQL, Credentials are stored under the security folder / credentials - that is where you create a credential and place your AD username / pwd. The proxy then asks you to select a credential from the list of credentials already stored on the system - not your domain\user at that point.