question

GMC avatar image
GMC 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....
ssissql-agentjob
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

GMC avatar image GMC commented ·
thanks for your reply.....and Yes, just because of this connection error ,from past one week i am running the job at 3 am manually and I had used administrative account to automate the job......
0 Likes 0 ·
Tim avatar image
Tim answered
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?
16 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image Tim commented ·
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.
0 Likes 0 ·
GMC avatar image GMC commented ·
my package is targetted to local system
0 Likes 0 ·
GMC avatar image GMC commented ·
sql server agent is configured to start automatically
0 Likes 0 ·
Tim avatar image Tim commented ·
@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.
0 Likes 0 ·
GMC avatar image GMC commented ·
SQL agent is running as local system
0 Likes 0 ·
Show more comments
Pavel Pawlowski avatar image
Pavel Pawlowski answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image Tim commented ·
+1, you beat my by 20 seconds. :)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.