Being able to holistically view your SQL Server Job Agent job schedules, how long each job took to run, what other jobs were running at the same time and the status of each job, should be a fundamental part of Job Agent management.

In this post I will be briefly introducing the current SSRS reports integrated into SSMS (or SQL Data Tools) that displays SQL Server Job Agent job information, as well as provide a new report (pictures above) that should give you a better holistic view of activities on your SQL Server Agent, as well as highlight any conflict in job schedules or times were no activity is occurring.

Read more →

There are multiple ways to access SQL Job Agent jobs execution statistics, such as:

• Through SQL Server Management Objects (SMO): which provide a programmatic way of accessing and controlling many SQL Server objects. The Smo.Agent namespace will provide the required objects for accessing job statistics such as the last time each job (or step) ran, the duration the job ran for and the returned status as well as many other relevant information regarding job execution.
• Through undocumented Stored Procedures such as master.dbo.xp_sqlagent_enum_jobs: Although this doesn’t provide a lot of flexibility, particularly if you are executing and consuming the data within the context of T-SQL, rather than programmatically through wrapper classes that simplifies handling stored procedures with multiple output tables. It is important to note that since this stored procedure is undocumented, Microsoft could potentially retire it without prior notice, leaving your code non-functional.
• Through Job Agent System Tables: In my opinion this provides the best and most supported method of access. for the rest of this post I will be discussing the different System Tables provided to access SQL Server Job Agent statistics and execution information.

Recently I’ve been getting the following error message while trying to create an SSIS 2012 step in SQL Server Job Agent, and setting the Parameters or Connection Strings for that step:

The property ‘ParameterName’ contains invalid characters as an object name. Remove the invalid characters. (Microsoft.SqlServer.Management.IntegrationServices)

This error emerges after confirming the newly created step (which will run the SSIS package) in SQL Server Job Agent, and after setting the parameter.

Read more →