November 10, 2008 at 11:47 am
Ok I have been working with SQL Server 05 for a few weeks now and I need to setup automated tasks. Currently I am uploading data to my server via SFTP and dropping off text files that I want to import into different tables depending on which text file I'm talking about.
So far I have been able to create the DSTX package and save them to the file system. After creating the dstx and double click the file to run them, they work. But when I set the DSTX packages up as a scheduled SQLSERVER task, they never run and the only errors i ever get are:
Executed as user: [omitted server name]\SYSTEM. The package execution failed. The step failed.
What should I do. I need to get these task working asap.
November 10, 2008 at 12:23 pm
When the package is run using SQL Server Agent it takes on the login of the Agent. Check the configuration manager for agent to see what is being used. This user must not have access to the text files which is causing the error. When you run the package is runs as you.
A couple of suggestions:
1. Change the sql server agent to a user who can get to the files.
2. Add an operator for Integration services that has access to all locations.
November 10, 2008 at 1:16 pm
ok. like I said im really new to sql server but I created a new login under security\login, that matchs the windows user account that was used to log into SQL Server Manager, and was the account used to create the DTSX package originally. Same outcome sadly, dts packages will not run via sql server schedualer.
November 10, 2008 at 1:23 pm
Go to the SQL Server Configuration Manger look at the user that is listed next to SQL Server Agent in the "Logged In As" column. This need to be a user that can access the folder. Unless you have changed it it will be "LocalSystem"
Change this and restart agent and it should work.
November 10, 2008 at 1:30 pm
I loaded SQL Server Configuration Manager but there is no tab, drop down, or menu that lets me see anything about any users.
November 10, 2008 at 1:32 pm
Click on 'SQL Server 2005 Service' and wait a few seconds all of the SQL services on that machine will appear. You do have to be local when you run this.
November 10, 2008 at 7:37 pm
It seems to be running under the local system accounts (Windows account) and since this same account is the administration account, so shouldnt SQL Server have rights to the whole system?
November 11, 2008 at 7:27 am
Local system is an account that only allows access to that machine in order to get off the machine when you are in a domain you should use a domain account. I believe you can change it to network service and then it could access a public folder on the network but I always have my servers running on a specific domain account.
The other option continues to be creating a proxy on a domain account that will allow the package to run.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply