CODEWITHSUNDEEP
sql-serverssissql-agent-job
Rameshwar Pawale
Rameshwar Pawale

Reputation: 682

SSIS Job Statistics

I am trying to find out SSIS Job Statistics Like

Jobs that are running per account (so how many and which jobs run from which system accounts vs which user accounts)

If I am calling a SSIS package from SSISDB Catalog, then I should know which account is calling that package. IS it a SQL Server Agent account or any other user Account.

Is there any way to get this info?

Thanks.

Upvotes: 1

Views: 1759

Answers (1)

Mark Wojciechowicz
Mark Wojciechowicz

Reputation: 4477

SSIS has some internal reports you could use. Below the Integration Services tab, you can right click on SSISDB and go to Reports > All Executions. This will give you all current and recent executions. If you click on Overview for a given execution, there is a field called CALLER_INFO which will tell you if this was called from SQL Agent. Note that if you are not an admin, certain executions may be filtered from your view in the report.

If you have permissions to query the SSISDB database directly, you can use the following which will give you the account name that called the execution as well as the account that is running the current operation:

select 
    execution_id
    , folder_name
    , project_name
    , package_name
    , environment_name
    , executed_as_name
    , start_time
    , end_time
    , CASE status WHEN 1 THEN 'Created'
        WHEN 2 THEN 'Running'
        WHEN 3 THEN 'Cancelled'
        WHEN 4 THEN 'Failed'
        WHEN 5 THEN 'Pending'
        WHEN 6 THEN 'Ended Unexpectedly'
        WHEN 7 THEN 'Succeeded'
        WHEN 8 THEN 'Stopping'
        ELSE 'Completed' END AS Status
    , caller_name
    , process_id
    , server_name
from internal.execution_info
--Optionally filter just running jobs
WHERE status = 2

This is a view which has the following filter:

WHERE      opers.[operation_id] in (SELECT id FROM [internal].[current_user_readable_operations])
           OR (IS_MEMBER('ssis_admin') = 1)
           OR (IS_SRVROLEMEMBER('sysadmin') = 1)

So if you are not an admin, the results will be filtered to those executions that you have permission to see. You could also query the internal tables directly to work around this limitation.

EDIT In the case of SQL Agent, you could use a query like below, which was adapted from this: https://www.mssqltips.com/sqlservertip/2561/querying-sql-server-agent-job-information/

The step will either be running under the database user name, proxy or the owner of the job, depending on how it's configured.

SELECT 
    [sJOB].[job_id] AS [JobID]
    , [sJOB].[name] AS [JobName]
    , [sDBP].[name] AS [JobOwner]
    , [sCAT].[name] AS [JobCategory]
    , [sJOB].[description] AS [JobDescription]
    , CASE [sJOB].[enabled]
        WHEN 1 THEN 'Yes'
        WHEN 0 THEN 'No'
      END AS [IsEnabled]
    , [sJOB].[date_created] AS [JobCreatedOn]
    , [sJOB].[date_modified] AS [JobLastModifiedOn]
    , [sSVR].[name] AS [OriginatingServerName]
    , [sJSTP].[step_id] AS [JobStartStepNo]
    , [sJSTP].[step_name] AS [JobStartStepName]
    , CASE
        WHEN [sSCH].[schedule_uid] IS NULL THEN 'No'
        ELSE 'Yes'
      END AS [IsScheduled]
    , [sSCH].[schedule_uid] AS [JobScheduleID]
    , [sSCH].[name] AS [JobScheduleName]
    , CASE [sJOB].[delete_level]
        WHEN 0 THEN 'Never'
        WHEN 1 THEN 'On Success'
        WHEN 2 THEN 'On Failure'
        WHEN 3 THEN 'On Completion'
      END AS [JobDeletionCriterion]
      , proxyName.name
      , sJSTP.database_user_name
FROM
    [msdb].[dbo].[sysjobs] AS [sJOB]
    LEFT JOIN [msdb].[sys].[servers] AS [sSVR]
        ON [sJOB].[originating_server_id] = [sSVR].[server_id]
    LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT]
        ON [sJOB].[category_id] = [sCAT].[category_id]
    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sJSTP]
        ON [sJOB].[job_id] = [sJSTP].[job_id]
        AND [sJOB].[start_step_id] = [sJSTP].[step_id]
    LEFT JOIN msdb.dbo.sysproxies proxy
        ON sJSTP.proxy_id = proxy.proxy_id
    LEFT JOIN [msdb].[sys].[database_principals] proxyName
        ON proxyName.sid = proxy.user_sid
    LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP]
        ON [sJOB].[owner_sid] = [sDBP].[sid]
    LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH]
        ON [sJOB].[job_id] = [sJOBSCH].[job_id]
    LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH]
        ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]

ORDER BY [JobName]

Upvotes: 4

Related Questions