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)

  • Read .NET configuration from database
  • SSRS positioning of an image
  • Copy Multiple Tables into ONE Table (From Multiple Databases)
  • How to filter SQL Server 2008 Profiler Output for a single database?
  • Do stored procedures lock tables/rows?
  • Why insert TSQL statement block when transaction isolation level for another transaction is serializable with non-conflicting filter?
  • 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?


  • SSIS 2012 SQL Agent Job ConnectionString vs InitialCatalog
  • SSIS package fails in sql server agent, but not in visual studio. ODBC Connection
  • Run C# code inside a SQL Agent Job
  • How to check in SQL Job step whether SSIS package exists or not?
  • SQLAgent job with different schedules
  • Windows Event Log Access from SSIS package run in SQL Agent Job
  • One Solution collect form web for “SSIS Job Statistics”

    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:

        , 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.

    In the case of SQL Agent, you could use a query like below, which was adapted from this:

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

        [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]
          , sJSTP.database_user_name
        [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]
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.