SQL Integration Service Test

A package is an organized collection of connections, control flow elements, data flow elements, event handlers, variables, parameters, and configurations, that you assemble using either the graphical design tools that SQL Server Integration Services provides, or build programmatically. You then save the completed package to SQL Server, the SSIS Package Store, or the file system, or you can deploy the ssISnoversion project to the SSIS server. The package is the unit of work that is retrieved, executed, and saved.

To run an Integration Services package, you can use one of several tools depending on where those packages are stored. For instance, packages that are stored in the file system outside of the SSIS Package Store can be run in SQL Server Data Tools (SSDT) during the development, debugging, and testing of packages. Likewise, the SQL Server Management Studio can be used to run a package that is stored on the Integration Services server itself. Alternatively, you can use the Execute Package utility to run such a package. You can also use utilities such as dtexec and dtexecui to run a package. While the former can be used regardless of where the package is stored, the latter is ideal for a package that is stored in the SSIS Package Store, or an msdb database, or a file system outside of the SSIS Package Store. Similarly, packages stored in SQL Server, the SSIS Package Store, and the file system, can be scheduled for automatic execution using the SQL Server Agent.

At any given point in time, more than one package can be run on the Integration server using any of the mechanisms discussed above. While this saves processing time, it can also be a drain on the server resources. By continuously tracking the package execution load on the server, administrators can proactively identify overload conditions that can cause serious resource contentions. This is where the SQL Integration Service test helps!

This test reports the count of packages that are being executed in parallel on the Integration server. This way, the test proactively alerts administrators to potential overload conditions, thus prompting them to either fine-tune the (SSIS) service configuration or increase server capacity to handle the anticipated load.

Target of the test : A Microsoft SQL Server Integration Services server

Agent deploying the test : An internal agent

Outputs of the test : One set of results for the server being monitored

Configurable parameters for the test

Parameters	Description
Test period	How often should the test be executed
Host	The host for which the test is to be configured.
Port	The port at which the specified Host listens.
SSIS Version	The eG agent runs queries on the Microsoft SQL server on which the SQL Server Integration Services (SSIS) are installed to pull the desired metrics. To enable the eG agent to run these queries, you need to configure the test with the version of SSIS that is in use in your environment. To know how to determine the SSIS version, refer to Configuring the eG Agent with the SSIS Version. Once the version number is ascertained, make sure you specify the exact number here.

Measurements made by the test

Measurement	Description	Measurement Unit	Interpretation
Simultaneous SSIS packages running	Indicates the number of packages currently running on SSIS.	Number	This is a good indicator of the current workload of the server. If the value of this measure rises consistently, it heralds an overload condition. To avoid such situations, the EngineThreads property can be set in according to the number of package executions (threads) needed. The SSIS engine itself will not use more threads than needed, and it will regard the set property’s value.