First published on MSDN on Aug 12, 2011
Today’s post is from Carla Sabotta, lead writer for the Integration Services team. It covers the stored procedures and Views that make up the API for the new SSIS Catalog in SQL Server Denali.
You can use Transact-SQL views and stored procedures to manage objects in the new Integration Services catalog (SSISDB), as well as configure the catalog. As you’ve read in previous blogs, the catalog is used for the execution and administration of SSIS projects and their associated packages, environments and parameters.
You can configure catalog properties by calling the
stored procedure and query the
view for the property settings.
Data and Performance
A data tap enables you to capture/log data as it flows through the package without modifying the package. You can add data taps to your packages, and remove the data taps, by calling the following stored procedures. .
To view information about data taps that have been added, query
To view information about execution statistics for executables in a package and child package, query catalog.executable_statistics. The catalog.executables view contains information about executables.
You can use the
view to compute data throughput for a component. You can find out how much time is spent by a data flow component in each execution phase, by querying the
function returns performance statistics for packages in an execution.
Projects and Packages
You can deploy, move and carry out other project admin tasks by calling the following stored procedures.
These views provide details about packages, projects, and project versions.
You use parameters to assign values to package properties at the time of package execution. To set the value of a package or project parameter and clear the value, call
. To set the value of a parameter for an instance of execution, call
. You can retrieve default parameter values by calling
These views show the parameters for all packages and projects, and parameter values that are used for an instance of execution.
Server Environments, Server Variables, and Server Environment References
Server environments contain server variables. The variable values can be used when a package is executed or validated on the Integration Services server.
The following stored procedures enable you to create, delete, move and perform many other management tasks for environments and variables.
By calling the
stored procedure, you can set the sensitivity bit for a variable.
To use the value of a server variable, you need to specify the reference between the project and the server environment. You can use the following stored procedures to create and delete references, and to indicate whether the environment can be located in the same folder as the project or in a different folder.
For more details about environments and variables, query these views.
Executions and Validations
An execution is an instance of a package execution. Call
to create and start an execution. To stop an execution or a package/project validation, call
To cause a running package to pause and create a dump file, call the
stored procedure. A dump file provides information about the execution of package that can help you troubleshoot execution issues.
For details about executions, validations, messages that are logged during operations, and contextual information related to errors, query these views.
You can validate projects and packages by calling the
stored procedures. The
view provides details about validations such as the server environment references that are considered in the validation, whether it is a dependency validation or a full validation, and whether the 32-bit runtime or the 64-bit runtime is used to run the package.
You can assign permissions to securable objects. Securable objects in the catalog are folders, projects, environments, and operations. You can use the following stored procedures to manage permissions.
for the permissions that are explicitly assigned to the user for securable objects. Query
for the permissions that are assigned to the current database principal for securable catalog objects.
You can create, delete, and rename catalog folders, as well as set descriptions by calling the following stored procedures.
You can query
for a list of folders in the catalog.