| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | ms.custom | f1_keywords | ||
|---|---|---|---|---|---|---|---|---|---|---|---|
Monitor Running Packages and Other Operations |
Monitor Running Packages and Other Operations |
chugugrace |
chugu |
06/04/2018 |
sql |
integration-services |
conceptual |
supportability |
|
[!INCLUDEsqlserver-ssis]
You can monitor [!INCLUDEssISnoversion] package executions, project validations, and other operations by using one of more of the following tools. Certain tools such as data taps are available only for projects that are deployed to the [!INCLUDEssISnoversion] server.
-
Logs
For more information, see Integration Services (SSIS) Logging.
-
Reports
For more information, see Reports for the Integration Services Server.
-
Views
For more information, see Views (Integration Services Catalog).
-
Performance counters
For more information, see Performance Counters.
-
Data taps
Note
This article describes how to monitor running SSIS packages in general, and how to monitor running packages on premises. You can also run and monitor SSIS packages in Azure SQL Database. For more info, see Lift and shift SQL Server Integration Services workloads to the cloud.
Although you can also run SSIS packages on Linux, no monitoring tools are provided on Linux. For more info, see Extract, transform, and load data on Linux with SSIS.
Several different types of operations are monitored in the SSISDB catalog, on the [!INCLUDEssISnoversion] server. Each operation can have multiple messages associated with it. Each message can be classified into one of several different types. For example, a message can be of type Information, Warning, or Error. For the full list of message types, see the documentation for the Transact-SQL catalog.operation_messages (SSISDB Database) view. For a full list of the operations types, see catalog.operations (SSISDB Database).
Nine different status types are used to indicate the status of an operation. For a full list of the status types, see the catalog.operations (SSISDB Database) view.
Use the Active Operations dialog box to view the status of currently running [!INCLUDEssISnoversion] operations on the [!INCLUDEssISnoversion] server, such as deployment, validation, and package execution. This data is stored in the SSISDB catalog.
For more information about related [!INCLUDEtsql] views, see catalog.operations (SSISDB Database), catalog.validations (SSISDB Database), and catalog.executions (SSISDB Database)
-
Open [!INCLUDEssNoVersion] [!INCLUDEssManStudio].
-
Connect Microsoft SQL Server Database Engine
-
In Object Explorer, expand the Integration Services node, right-click SSISDB, and then click Active Operations.
Type
Specifies the type of operation. The following are the possible values for the Type field and the corresponding values in the operations_type column of the Transact-SQL catalog.operations view.
| Type field description | operations_type value |
|---|---|
| Integration Services initialization | 1 |
| Operations cleanup (SQL Agent job) | 2 |
| Project versions cleanup (SQL Agent job) | 3 |
| Deploy project | 101 |
| Restore project | 106 |
| Create and start package execution | 200 |
| Stop operation (stopping a validation or execution | 202 |
| Validate project | 300 |
| Validate package | 301 |
| Configure catalog | 1000 |
Stop
Click to stop a currently running operation.
The SSISDB database stores execution history in internal tables that are not visible to users. However it exposes the information that you need through public views that you can query. It also provides stored procedures that you can call to perform common tasks related to packages.
Typically you manage [!INCLUDEssISnoversion] objects on the server in [!INCLUDEssManStudioFull]. However you can also query the database views and call the stored procedures directly, or write custom code that calls the managed API. [!INCLUDEssManStudioFull] and the managed API query the views and call the stored procedures to perform many of their tasks. For example, you can view the list of [!INCLUDEssISnoversion] packages that are currently running on the server, and request packages to stop if you have to.
You can view the list of packages that are currently running on the server in the Active Operations dialog box. For more information, see Active Operations Dialog Box.
For information about the other methods that you can use to view the list of running packages, see the following topics.
[!INCLUDEtsql] access
To view the list of packages that are running on the server, query the view, catalog.executions (SSISDB Database) for packages that have a status of 2.
Programmatic access through the managed API
See the xref:Microsoft.SqlServer.Management.IntegrationServices namespace and its classes.
You can request a running package to stop in the Active Operations dialog box. For more information, see Active Operations Dialog Box.
For information about the other methods that you can use to stop a running package, see the following topics.
[!INCLUDEtsql] access
To stop a package that is running on the server, call the stored procedure, catalog.stop_operation (SSISDB Database).
Programmatic access through the managed API
See the xref:Microsoft.SqlServer.Management.IntegrationServices namespace and its classes.
To view the history of packages that have run in [!INCLUDEssManStudio], use the All Executions report. For more information on the All Executions report and other standard reports, see Reports for the Integration Services Server.
For information about the other methods that you can use to view the history of running packages, see the following topics.
[!INCLUDEtsql] access
To view information about packages that have run, query the view, catalog.executions (SSISDB Database).
Programmatic access through the managed API
See the xref:Microsoft.SqlServer.Management.IntegrationServices namespace and its classes.
In the current release of [!INCLUDEssNoVersion] [!INCLUDEssISnoversion], standard reports are available in [!INCLUDEssManStudioFull] to help you monitor [!INCLUDEssISnoversion] projects that have been deployed to the [!INCLUDEssISnoversion] server. These reports help you to view package status and history, and, if necessary, identify the cause of package execution failures.
At the top of each report page, the back icon takes you to the previous page you viewed, the refresh icon refreshes the information displayed on the page, and the print icon allows you to print the current page.
For information on how to deploy packages to the [!INCLUDEssISnoversion] server, see Deploy Integration Services (SSIS) Projects and Packages.
The Integration Services Dashboard report provides an overview of all the package executions on the [!INCLUDEssNoVersion] instance. For each package that has run on the server, the dashboard allows you to "zoom in" to find specific details on package execution errors that may have occurred.
The report displays the following sections of information.
| Section | Description |
|---|---|
| Execution Information | Shows the number of executions that are in different states (failed, running, succeeded, others) in the past 24 hours. |
| Package Information | Shows the total number of packages that have been executed in the past 24 hours. |
| Connection Information | Shows the connections that have been used in failed executions in the past 24 hours. |
| Package Detailed Information | Shows the details of the completed executions that have occurred in the past 24 hours. For example, this section shows the number of failed executions versus the total number of executions, the duration of an executions (in seconds), and the average duration of executions for over the past three months. You can view additional information for a package by clicking Overview, All Messages, and Execution Performance. The Execution Performance report shows the duration of the last execution instance, as well as the start and end times, and the environment that was applied. The chart and associated table included in the Execution Performance report shows the duration of the past 10 successful executions of the package. The table also shows the average execution duration over a three-month period. Different environments and different literal values may have been applied at runtime for these 10 successful executions of the package. Finally, the Execution Performance report shows the Active Time and Total Time for the package data flow components. The Active Time refers to the total amount of time that component has spent executing in all phases, and the Total Time refers to the total time elapsed for a component. The report only displays this information for package components when the logging level of the last package execution was set to Performance or Verbose. The Overview report shows the state of package tasks. The Messages report shows the event messages and error messages for the package and tasks, such as reporting the start and end times, and the number of rows written. You can also click View Messages in the Overview report to navigate to the Messages report. You can also click View Overview in the Messages report to navigate to the Overview report. |
You can filter the table displayed on any page by clicking Filter and then selecting criteria in the Filter Settings dialog. The filter criteria that are available depend on the data being displayed. You can change the sort order of the report by clicking the sort icon in the Filter Settings dialog.
The All Executions Report displays a summary of all [!INCLUDEssISnoversion] executions that have been performed on the server. There can be multiple executions of the sample package. Unlike the Integration Services Dashboard report, you can configure the All Executions report to show executions that have started during a range of dates. The dates can span multiple days, months, or years.
The report displays the following sections of information.
| Section | Description |
|---|---|
| Filter | Shows the current filter applied to the report, such as the Start time range. |
| Execution Information | Shows the start time, end time, and duration for each package execution.You can view a list of the parameter values that were used with a package execution, such as values that were passed to a child package using the Execute Package task. To view the parameter list, click Overview. |
For more information about using the Execute Package task to make values available to a child package, see Execute Package Task.
For more information about parameters, see Integration Services (SSIS) Package and Project Parameters.
The All Connections report provides the following information for connections that have failed, for executions that have occurred on the [!INCLUDEssNoVersion] instance.
The report displays the following sections of information.
| Section | Description |
|---|---|
| Filter | Shows the current filter applied to the report, such as connections with a specified string and the Last failed time range. You set the Last failed time range to display only connection failures that occurred during a range of dates. The range can span multiple days, months, or years. |
| Details | Shows the connection string, number of executions during which a connection failed, and the date when the connection last failed. |
The All Operations Report displays a summary of all [!INCLUDEssISnoversion] operations that have been performed on the server, including package deployment, validation, and execution, as well as other administrative operations. As with the Integration Services Dashboard, you can apply a filter to the table to narrow down the information displayed.
The All Validations Report displays a summary of all [!INCLUDEssISnoversion] validations that have been performed on the server. The summary displays information for each validation such as status, start time, and end time. Each summary entry includes a link to messages generated during validation. As with the Integration Services Dashboard, you can apply a filter to the table to narrow down the information displayed.
You can add a custom report (.rdl file) to the SSISDB catalog node under the Integration Services Catalogs node in [!INCLUDEssManStudioFull]. Before adding the report, confirm that you are using a three-part naming convention to fully qualify the objects you reference such as a source table. Otherwise, [!INCLUDEssManStudioFull] will display an error. The naming convention is <database>.<owner>.<object>. An example would be SSISDB.internal.executions.
Note
When you add custom reports to the SSISDB node under the Databases node, the SSISDB prefix is not necessary.
For instructions on how to create and add a custom report, see Add a Custom Report to Management Studio.
In the current release of [!INCLUDEssNoVersion] [!INCLUDEssISnoversion], standard reports are available in [!INCLUDEssManStudioFull] to help you monitor [!INCLUDEssISnoversion] projects that have been deployed to the [!INCLUDEssISnoversion] server. For more information about the reports, see Reports for the Integration Services Server.
-
In [!INCLUDEssManStudioFull], expand the Integration Services Catalogs node in Object Explorer.
-
Right-click SSISDB, click Reports, and then click Standard Reports.
-
Click one more of the following to view a report.
-
Integration Services Dashboard
-
All Executions
-
All Validations
-
All Operations
-
All Connections
-
Execution of Projects and Packages
Troubleshooting Reports for Package Execution