Creates a new collection set. You can use this stored procedure to create a custom collection set for data collection.
In cases where the Windows account configured as a proxy is a non-interactive or interactive user that has not yet logged in, the profile directory will not exist, and the creation of the staging directory will fail. Therefore, if you are using a proxy account on a domain controller, you must specify an interactive account that has been used at least once in order to assure that the profile directory has been created.
Is the number of days that the collected data is saved in the management data warehouse. days_until_expiration is smallint with a default value of 730 (two years). days_until_expiration must be 0 or a positive integer.
[ @proxy_id = ] proxy_id
Is the unique identifier for a SQL Server Agent proxy account. proxy_id is int with a default value of NULL. If specified, proxy_name must be NULL. To obtain proxy_id, query the sysproxies system table. The dc_admin fixed database role must have permission to access the proxy. For more information, see Create a SQL Server Agent Proxy.
[ @proxy_name = ] 'proxy_name'
Is the name of the proxy account. proxy_name is sysname with a default value of NULL. If specified, proxy_id must be NULL. To obtain proxy_name, query the sysproxies system table.
[ @schedule_uid = ] 'schedule_uid'
Is the GUID that points to a schedule. schedule_uid is uniqueidentifier with a default value of NULL. If specified, schedule_name must be NULL. To obtain schedule_uid, query the sysschedules system table.
When collection_mode is set to 0, schedule_uid or schedule_name must be specified. When collection_mode is set to 1, schedule_uid or schedule_name is ignored if specified.
[ @schedule_name = ] 'schedule_name'
Is the name of the schedule. schedule_name is sysname with a default value of NULL. If specified, schedule_uid must be NULL. To obtain schedule_name, query the sysschedules system table.
[ @logging_level = ] logging_level
Is the logging level. logging_level is smallint with one of the following values:
0 - log execution information and SSIS events that track:
Starting/stopping collection sets
1 - level-0 logging and:
Continuously running collection progress
Warning events from SSIS
2 - level-1 logging and detailed event information from SSIS
The default value for logging_level is 1.
[ @description = ] 'description'
Is the description of the collection set. description is nvarchar(4000) with a default value of NULL.
[ @collection_set_id = ] collection_set_id
Is the unique local identifier for the collection set. collection_set_id is int with OUTPUT and is required.
[ @collection_set_uid = ] 'collection_set_uid'
Is the GUID for the collection set. collection_set_uid is uniqueidentifier with OUTPUT with a default value of NULL.
A. Creating a collection set by using default values
The following example creates a collection set by specifying only the required parameters. @collection_mode is not required, but the default collection mode (cached) requires specifying either a schedule ID or schedule name.
DECLARE @collection_set_id int;
@name = N'Simple collection set test 1',
@description = N'This is a test collection set that runs in non-cached mode.',
@collection_mode = 1,
@collection_set_id = @collection_set_id OUTPUT;
B. Creating a collection set by using specified values
The following example creates a collection set by specifying values for many of the parameters.
DECLARE @collection_set_id int;
DECLARE @collection_set_uid uniqueidentifier;
SET @collection_set_uid = NEWID();
@name = N'Simple collection set test 2',
@collection_mode = 0,
@days_until_expiration = 365,
@description = N'This is a test collection set that runs in cached mode.',
@logging_level = 2,
@schedule_name = N'CollectorSchedule_Every_30min',
@collection_set_id = @collection_set_id OUTPUT,
@collection_set_uid = @collection_set_uid OUTPUT;