December 6, 2012

Data Collector Views

Se more view version maps here: Microsoft Sql System View Version Maps

syscollector_collection_items

Returns information about an item in a collection set.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
collection_set_id   int Identifies the collection set. Is not nullable.
collection_item_id   int Identifies an item in the collection set. Is not nullable.
collector_type_uid   uniqueidentifier The GUID used to identify the collector type. Is not nullable.
name   nvarchar(4000) The name of the collection set. Is nullable.
frequency   int The frequency that data is collected by a collection item. Is not nullable.
parameters   xml Describes the parameterization for the collector type associated with the collection item. The XML schema for this collection item is validated with the XML Schema (XSD) stored in the parameter_schema for a particular collector type. Is nullable. For more information, see syscollector_collector_types (Transact-SQL)1.

TSQL

Sql 2008
SELECT [collection_set_id], [collection_item_id], [collector_type_uid], [name], [frequency], [parameters] FROM syscollector_collection_items
Sql 2008 R2
SELECT [collection_set_id], [collection_item_id], [collector_type_uid], [name], [frequency], [parameters] FROM syscollector_collection_items
Sql 2012
SELECT [collection_set_id], [collection_item_id], [collector_type_uid], [name], [frequency], [parameters] FROM syscollector_collection_items

Back to Top


syscollector_collection_sets

Provides information about a collection set, including schedule, collection mode, and its state.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
collection_set_id   int The local identifier for the collection set. Is not nullable.
collection_set_uid   uniqueidentifier The globally unique identifier for the collection set. Is not nullable.
name   nvarchar(4000) The name of the collection set. Is nullable.
target   nvarchar(max) Identifies the target for the collection set. Is nullable.
is_system   bit Turned on (1) or off (0) to indicate if the collection set was included with the data collector or if it was added later by the dc_admin. This could be a custom collection set developed in-house or by a third party. Is not nullable.
is_running   bit Indicates whether or not the collection set is running. Is not nullable.
collection_mode   smallint Specifies the collection mode for the collection set. Is not nullable.
Collection mode is one of the following:
0 - Cached mode. Data collection and upload are on separate schedules.
1 - Non-cached mode. Data collection and upload are on the same schedule.
proxy_id   int Identifies the proxy that is used to run the collection set job step. Is nullable.
schedule_uid   uniqueidentifier Provides a pointer to the collection set schedule. Is nullable.
collection_job_id   uniqueidentifier Identifies the collection job. Is nullable.
upload_job_id   uniqueidentifier Identifies the collection upload job. Is nullable.
logging_level   smallint Specifies the logging level (0, 1 or 2). Is not nullable. For more information about logging levels, see Data Collector Logging1.
days_until_expiration   smallint The number of days that the collected data is saved in the management data warehouse. Is not nullable.
description   nvarchar(4000) Describes the collection set. Is nullable.
dump_on_any_error   bit Turned on (1) or off (0) to indicate whether to create an SSIS dump file on any error. Is not nullable.
dump_on_codes   nvarchar(max) Contains the list of SSIS error codes that are used to trigger the dump file. Is nullable.
For more information about how to obtain a dump file, see the How to enable the Sqldumper.exe utility to generate dump files for processes that are related to SQL Server 2005 Integration Services with Service Pack 22 article in the Microsoft Knowledge Base.

TSQL

Sql 2008
SELECT [collection_set_id], [collection_set_uid], [name], [target], [is_system], [is_running], [collection_mode], [proxy_id], [schedule_uid], [collection_job_id], [upload_job_id], [logging_level], [days_until_expiration], [description], [dump_on_any_error], [dump_on_codes] FROM syscollector_collection_sets
Sql 2008 R2
SELECT [collection_set_id], [collection_set_uid], [name], [target], [is_system], [is_running], [collection_mode], [proxy_id], [schedule_uid], [collection_job_id], [upload_job_id], [logging_level], [days_until_expiration], [description], [dump_on_any_error], [dump_on_codes] FROM syscollector_collection_sets
Sql 2012
SELECT [collection_set_id], [collection_set_uid], [name], [target], [is_system], [is_running], [collection_mode], [proxy_id], [schedule_uid], [collection_job_id], [upload_job_id], [logging_level], [days_until_expiration], [description], [dump_on_any_error], [dump_on_codes] FROM syscollector_collection_sets

Back to Top


syscollector_collector_types

Provides information about a collector type for a collection item.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
collector_type_uid   uniqueidentifer The GUID for a collection type. Is not nullable.
name   sysname The name of the collection type. Is not nullable.
parameter_schema   xml The XML schema that describes what the configuration for the specified collector type looks like. This XML schema is used to validate the actual XML configuration associated with a particular collection item instance. Is nullable.
parameter_formatter   xml Determines the template to use to transform the XML for use in the collection set property page. Is nullable.
collection_package_id   uniqueidentifer The GUID for a collection package. Is not nullable.
collection_package_path   nvarchar(4000) Provides the path to the collection package. Is nullable.
collection_package_name   sysname The name of the collection package. Is not nullable.
upload_package_id   uniqueidentifer The GUID for the upload package. Is not nullable.
upload_package_path   nvarchar(4000) Provides the path to the upload package. Is nullable.
upload_package_name   sysname The name of the upload package. Is not nullable.
is_system   bit Turned on (1) or off (0) to indicate if the collector type was shipped with the data collector or if it was added later by the dc_admin. This could be a custom type developed in-house or by a third party. Is not nullable.

TSQL

Sql 2008
SELECT [collector_type_uid], [name], [parameter_schema], [parameter_formatter], [collection_package_id], [collection_package_path], [collection_package_name], [upload_package_id], [upload_package_path], [upload_package_name], [is_system] FROM syscollector_collector_types
Sql 2008 R2
SELECT [collector_type_uid], [name], [parameter_schema], [parameter_formatter], [collection_package_id], [collection_package_path], [collection_package_name], [upload_package_id], [upload_package_path], [upload_package_name], [is_system] FROM syscollector_collector_types
Sql 2012
SELECT [collector_type_uid], [name], [parameter_schema], [parameter_formatter], [collection_package_id], [collection_package_path], [collection_package_name], [upload_package_id], [upload_package_path], [upload_package_name], [is_system] FROM syscollector_collector_types

Back to Top


syscollector_config_store

Returns properties that apply to the entire data collector, as opposed to a collection set instance. Each row in this view describes a specific data collector property, such as the name of the management data warehouse, and the instance name where the management data warehouse is located.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
parameter_name   nvarchar(128) The name of the property. Is not nullable.
parameter_value   sql_variant The actual value of the property. Is nullable.

TSQL

Sql 2008
SELECT [parameter_name], [parameter_value] FROM syscollector_config_store
Sql 2008 R2
SELECT [parameter_name], [parameter_value] FROM syscollector_config_store
Sql 2012
SELECT [parameter_name], [parameter_value] FROM syscollector_config_store

Back to Top


syscollector_execution_log

Provides information from the execution log for a collection set or package.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
log_id   bigint Identifies each collection set execution. Used to join this view with other detailed logs. Is not nullable.
parent_log_id   bigint Identifies the parent package or collection set. Is not nullable. The IDs are chained in the parent-child relationship, which enables you to determine which package was started by which collection set. This view groups the log entries by their parent-child linkage and indents the names of the packages, so that the call chain is clearly visible.
collection_set_id   int Identifies the collection set or package that this log entry represents. Is not nullable.
collection_item_id   int Identifies a collection item. Is nullable.
start_time   datetime The time that the collection set or package started. Is not nullable.
last_iteration_time   datetime For continuously running packages, the last time that the package captured a snapshot. Is nullable.
finish_time   datetime The time the run completed for finished packages and collection sets. Is nullable.
runtime_execution_mode   smallint Indicates whether the collection set activity was collecting data or uploading data. Is nullable.
Values are:
0 = Collection
1 = Upload
status   smallint Indicates the current status of the collection set or package. Is not nullable.
Values are:
0 = running
1 = finished
2 = failed
operator   nvarchar(128) Identifies who started the collection set or package. Is not nullable.
package_id   uniqueidentifier Identifies the collection set or package that generated this log. Is nullable.
package_name   nvarchar(4000) The name of the package that generated this log. Is nullable.
package_execution_id   uniqueidentifier Provides a link to the SSIS log table. Is nullable.
failure_message   nvarchar(2048) If the collection set or package failed, the most recent error message for that component. Is nullable. To obtain more detailed error information, use the fn_syscollector_get_execution_details (Transact-SQL)1 function.

TSQL

Sql 2008
SELECT [log_id], [parent_log_id], [collection_set_id], [collection_item_id], [start_time], [last_iteration_time], [finish_time], [runtime_execution_mode], [status], [operator], [package_id], [package_name], [package_execution_id], [failure_message] FROM syscollector_execution_log
Sql 2008 R2
SELECT [log_id], [parent_log_id], [collection_set_id], [collection_item_id], [start_time], [last_iteration_time], [finish_time], [runtime_execution_mode], [status], [operator], [package_id], [package_name], [package_execution_id], [failure_message] FROM syscollector_execution_log
Sql 2012
SELECT [log_id], [parent_log_id], [collection_set_id], [collection_item_id], [start_time], [last_iteration_time], [finish_time], [runtime_execution_mode], [status], [operator], [package_id], [package_name], [package_execution_id], [failure_message] FROM syscollector_execution_log

Back to Top


syscollector_execution_log_full

Provides information about a collection set or package when the execution log is full.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
log_id   bigint Identifies each collection set execution. Used to join this view with other detailed logs. Is nullable.
parent_log_id   bigint Identifies the parent package or collection set. Is not nullable. The IDs are chained in the parent-child relationship, which enables you to determine which package was started by which collection set. This view groups the log entries by their parent-child linkage and indents the names of the packages so that the call chain is clearly visible.
name   nvarchar(4000) The name of the collection set or package that this log entry represents. Is nullable.
status   smallint Indicates the current status of the collection set or package. Is nullable.
Values are:
0 = running
1 = finished
2 = failed
runtime_execution_mode   smallint Indicates whether the collection set activity was collecting data or uploading data. Is nullable.
start_time   datetime The time that the collection set or package started. Is nullable.
last_iteration_time   datetime For continuously running packages, the last time that the package captured a snapshot. Is nullable.
finish_time   datetime The time the run completed for finished packages and collection sets. Is nullable.
duration   int The time, in seconds, that the package or collection set has been running. Is nullable.
failure_message   nvarchar(2048) If the collection set or package failed, the most recent error message for that component. Is nullable. To obtain more detailed error information, use the fn_syscollector_get_execution_details (Transact-SQL)1 function.
operator   nvarchar(128) Identifies who started the collection set or package. Is nullable.
package_execution_id   uniqueidentifier Provides a link to the SSIS log table. Is nullable.
collection_set_id   int Provides a link to the data collection configuration table in msdb. Is nullable.

TSQL

Sql 2008
SELECT [log_id], [parent_log_id], [name], [status], [runtime_execution_mode], [start_time], [last_iteration_time], [finish_time], [duration], [failure_message], [operator], [package_execution_id], [collection_set_id] FROM syscollector_execution_log_full
Sql 2008 R2
SELECT [log_id], [parent_log_id], [name], [status], [runtime_execution_mode], [start_time], [last_iteration_time], [finish_time], [duration], [failure_message], [operator], [package_execution_id], [collection_set_id] FROM syscollector_execution_log_full
Sql 2012
SELECT [log_id], [parent_log_id], [name], [status], [runtime_execution_mode], [start_time], [last_iteration_time], [finish_time], [duration], [failure_message], [operator], [package_execution_id], [collection_set_id] FROM syscollector_execution_log_full

Back to Top


syscollector_execution_stats

Provides information about task execution for a collection set or package.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
log_id   bigint Identifies each collection set execution. Used to join this view with other detailed logs. Is not nullable.
task_name   nvarchar(128) The name of the collection set or package task that this information is for. Is not nullable.
execution_row_count_in   int Number of rows processed at the beginning of data flow. Is nullable.
execution_row_count_out   int Number of rows processed at the end of data flow. Is nullable.
execution_row_count_errors   int Number of rows that failed during the data flow. Is nullable.
execution_time_ms   int The time, in milliseconds, required for the task to complete. Is nullable.
log_time   datetime The time that this information was logged. Is not nullable.

TSQL

Sql 2008
SELECT [log_id], [task_name], [execution_row_count_in], [execution_row_count_out], [execution_row_count_errors], [execution_time_ms], [log_time] FROM syscollector_execution_stats
Sql 2008 R2
SELECT [log_id], [task_name], [execution_row_count_in], [execution_row_count_out], [execution_row_count_errors], [execution_time_ms], [log_time] FROM syscollector_execution_stats
Sql 2012
SELECT [log_id], [task_name], [execution_row_count_in], [execution_row_count_out], [execution_row_count_errors], [execution_time_ms], [log_time] FROM syscollector_execution_stats

Back to Top

No comments:

Post a Comment

Total Pageviews