Integration Services components

SQLWATCH Central Repostory requires Integration Services package in order to collect data from remote instance. It can be deployed from Visual Studio project of ISPAC included in the release.

The Project consists of two packages: Control Package and the Worker Package.

Package Configuration

Control package

The control package control_import.dtsx is responsible for orchestrating multi-threaded data collection and execution of the Worker Package import_remote_data.dtsx

Parameters

number_of_parallel_collectors Number of threads for parallel collection. If this is set to > 1, then multiple servers will be collected in parallel, in addition to each collector data flow being run in parallel, according to the MaxConcurrentExecutables parameter. Be careful as running parallel collectors may be slower than single thread. Make sure central repository can sustain the workload. Maximum allowed parallel threads are 8.

repository_database Name of the database where the central repository is. Default SQLWATCH.

repository_instance_name Name of the SQL Server instance where the central repository is hosted.

repository_password SQL Password to access central repository or blank for Windows authentication.

repository_user_name SQL User to access central repository or blank for Windows authentication.

Worker Package

The worker package import_remote_data.dtsx is responsible for the actual data collection from remote instances into the central repository.

Parameters

last_snapshot_offset_minutes Offset in minutes to increase delta time slice. By default only data since last snapshot will be collected from the remote instance. We may increase this and go further beyond that to cover any gaps. Behind the scenes this translates to [snapshot_time] > dateadd(minute,-@last_snapshot_offset_minutes,[last_snapshot_time])

Not currently used, reserved for future use

remote_instance_name SQL Instance to collect data from. This parameters is passed from the control package during run time.

remote_password SQL Password for the remote instance or blank for Windows authentication.

remote_user_name SQL User for the remote instance or blank for Windows authentication.

In the current implementation it is not possible to specify different accounts for accessing remote instances. This means that when using SQL Authentication all instances must have the same SQL User and Password. When using Windows authentication this is usually not a problem as the SSIS runs under a context of one the SQL agent or proxy account

repository_database Name of the database where the central repository is. Default SQLWATCH.

repository_instance_name Name of the SQL Server instance where the central repository is hosted.

repository_password SQL Password to access central repository or blank for Windows authentication.

repository_user_name SQL User to access central repository or blank for Windows authentication.