70-463 · Question #45
A SQL Server Integration Services (SSIS) package was deployed two weeks ago with the Project Deployment Model. Sometimes the package is started as part of a multistep SQL job. At other times, the…
The correct answer is A. In the SSISDB.[catalog], query the .[executions] view. The SSISDB catalog.executions view records each package execution along with the authenticated caller, making it the authoritative source for identifying who started a package regardless of how it was initiated.
Question
A SQL Server Integration Services (SSIS) package was deployed two weeks ago with the Project Deployment Model. Sometimes the package is started as part of a multistep SQL job. At other times, the package is started manually by a database administrator by using the Object Explorer in SQL Server Management Studio. You need to identify the authenticated user responsible for starting the package each time it executes. How can you find this information?
Options
- AIn the SSISDB.[catalog], query the .[executions] view.
- BIn the SSISDB.[catalog] , query the [event_messages] view.
- CIn SQL Server Management Studio, view the SQL Agent Job History.
- DIn SQL Server Management Studio, view the SQL Agent Error Log.
- EIn SQL Server Management Studio, view the SQL Server Log.
How the community answered
(15 responses)- A87% (13)
- B7% (1)
- E7% (1)
Why each option
The SSISDB catalog.executions view records each package execution along with the authenticated caller, making it the authoritative source for identifying who started a package regardless of how it was initiated.
The SSISDB.[catalog].[executions] view contains a row for every SSIS package execution and includes the caller_name column, which stores the Windows authenticated user or SQL Agent service account that initiated the execution. This view captures executions started both by SQL Agent jobs and manually through SSMS Object Explorer, providing a unified audit trail without needing to cross-reference multiple log sources.
The [catalog].[event_messages] view contains logged messages, warnings, and errors generated during execution but does not directly record the authenticated identity of the user who started the package.
The SQL Agent Job History shows the history of job step outcomes for scheduled jobs but does not capture executions that were started manually outside of SQL Agent.
The SQL Agent Error Log records SQL Server Agent service-level errors and warnings, not SSIS execution initiation or authenticated user information.
The SQL Server Log records SQL Server instance-level events such as startups and logins, not SSIS package execution metadata or caller identity.
Concept tested: Querying SSISDB catalog.executions for package execution audit
Source: https://learn.microsoft.com/en-us/sql/integration-services/system-views/catalog-executions-ssisdb-database
Topics
Community Discussion
No community discussion yet for this question.