My SQL Agent was working fine. Now when I try to run it, it failes when trying to run my SSIS package. I looked in the logs, and job history, no luck in finding any critical errors.
My SSIS package runs fine through VS 2005 in debug mode.
Here are the only errors:
01/04/2006 09:53:48,,Warning,[396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect
01/04/2006 09:53:48,,Warning,[260] Unable to start mail session (reason: No mail profile defined)
01/04/2006 09:53:48,,Information,[129] SQLSERVERAGENT starting under Windows NT service control
01/04/2006 09:53:48,,Error,[364] The Messenger service has not been started - NetSend notifications will not be sent
01/04/2006 09:53:30,,Information,[432] There are 11 subsystems in the subsystems cache
01/04/2006 09:53:29,,Information,[339] Local computer is BG-SQL2005 running Windows NT 5.2 (3790) Service Pack 1
01/04/2006 09:53:29,,Information,[310] 1 processor(s) and 512 MB RAM detected
01/04/2006 09:53:29,,Information,[103] NetLib being used by driver is DBNETLIB.DLL; Local host server is
01/04/2006 09:53:29,,Information,[102] SQL Server ODBC driver version 9.00.1399
01/04/2006 09:53:29,,Information,[101] SQL Server BG-SQL2005 version 9.00.1399 (0 connection limit)
01/04/2006 09:53:29,,Information,[100] Microsoft SQLServerAgent version 9.00.1399.06 (x86 unicode retail build) : Process ID 1860
SQL Agent Failing when trying to run my SSIS Package
kceng123
If your using NetSend to notify clients of success and/or failure, you should also confirm that the service is started on the clients, otherwise they will never receive the message.
Larry
blackArt
David Riddiford
Make sure you select all events on the details pane. Also point the file to a folder that exists on the server, otherwise you'll need to override the connection string in the job.
The result should give you a whole lot of data but it should give you a more useful error message.
Larry
vbVet
But the package isn't failing. I can run it fine by:
1) Right clicking it in in Integration Services Connection in Server Management Studio and Executing it
2) Running it from my package in VS 2005.
So it's not the package, and therefore I don't know why yesterday my agent could run it fine and today it errors out. As far as I know, the network guys haven't touched my server.
Screen Shots of my setup:
http://www.photopizzaz.biz/sql_agent_problem.doc
Lars Lautrup-Larsen
I just gave the NT AUTHORITY\SYSTEM all roles, and my agent is still failing.
How do I enable logging I believe it's already enabled, I can right click my Integration Services connection in Management Stuido and select "View logs" and the logs aren't giving me any useful information or any logs on the process unless I executed my package from Integration Services.
bobbyp
01/04/2006 10:42:56,Start EBN Process,Error,1,BG-SQL2005,Start EBN Process,Start EBN SSIS,,Executed as user: BG-SQL2005\SYSTEM. The package execution failed. The step failed.,00:00:07,0,0,,,,0
and error from profiler reads:
EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @job_id = 0xCDE36535B5759E4C82D8110AC8514DF1, @step_id = 1, @sql_message_id = 0, @sql_severity = 0, @run_status = 0, @run_date =
20060104, @run_time = 104919, @run_duration = 36, @operator_id_emailed = 0, @operator_id_netsent = 0, @operator_id_paged = 0, @retries_attempted = 0, @session_id = 4, @message =
N'Executed as user: BG-SQL2005\SYSTEM. The package execution failed. The step failed.'
johnconway26
also, when I try to specify the owner of my sql job, I select NT AUTHORITY\SYSTEM. Then it comes up with another box with NT AUTHORITY\SYSTEM again and says the following (print screen):
http://www.photopizzaz.biz/owner.doc
Also, I don't know if this is correct but when I select my SSIS package from within my job properties, I could have sworn that the icon next to my SSIS package name wasn't greyed out as it is now..although I could be wrong. Are the icons usually grey next to the package names when browsing to your packages when you click on "Package" when defining a step in a sql job for an SSIS step
chucho
By how you have the job configured, when it is run via the scheduled job it is actually being run via the SQL Agent Login, which if you haven't modified the service should be NT Authority\System or %Hostname%\System. The logs files you posted suggest that the service has not been modified.
You may want to check the server role that is assigned to NT Authority\System. I believe by default it is sysadmin.
Enabling text logging will definitely help you identify where in the package the error occurs.
Larry
Rags_Bhat
renen
No matter what sql login I used other than a windows domain login, I can't connect to my database. I tried creating a new user called sqlapps2 gave it rights but I can't connect using that login when trying to connect to my database either in Management Studio using SQL Authentification. I am sure I'm typing in the password correctly but I just keep getting this error:
TITLE: Connect to Server------------------------------
Cannot connect to (local).
------------------------------
ADDITIONAL INFORMATION:
Login failed for user 'sqlapps'. (Microsoft SQL Server, Error: 18456)
For help, click: http:
------------------------------
BUTTONS:
OK
------------------------------
hardday
brad7x7
RyanTsai
Larry
Galan
The problem has to be with the security context under which the job runs, so you have to enable logging and run the job via the scheduled job to get the info you need to resolve the issue.
The log files may not tell you exactly why it is failing, but at least you can tell where in the process it is failing. By looking at the log files, you should be able to tell things like has the package validated all connections and metadata.
Once you run the package with logging to a text file via the scheduled job, look for an event called "OnError" and look at the message associated with that record.
Larry