asked
connection errors while running the ssis automated job
hai, I have a SSIS PACKAGE which is scheduled to run at 3 AM daily. My package is being stopped and when i check in profiler I get "Executing 'SP_reset_Connection' error " .... But when i manually run the same package its not giving any error. could somebody explain me why is this happening when job is automated....
When you manually run it are you running it at 3:00 AM as well? Does the service account that SQL Server Agent is running under have appropriate permissions to perform all the tasks you are executing in your SSIS package?
So you run the package manually at 3:00 AM and it runs fine, but when called from a sql job it fails. How is your SQL Server Agent Service configured? Is it using a specific login or running as Network Service or Local System? It needs to be running under an account with permissions to run the jobs or use a proxy account.
@GMChowdary, can you tell me who sql server agent is configured to run under? click start, run, type services.msc, scroll down to sql server agent for the instance you are working on and double click on sql server agent. Then click on the logon tab. On my servers I configure this to be a specific local user or domain user who has proper rights on my database instance.
You have to remember that when a package is executed from within a job, then it is executed under the agent service account, unless other proxy account is specified for execution. When you run the package manually you are executing the package under your account. You have to ensure, that the agent service account (or the proxy account used for execution) has appropriate rights.
