SSIS T-SQL API Overview

[SSIS-Team]

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 catalog.configure_catalog stored procedure and query the catalog.catalog_properties 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. .

• Catalog.add_data_tap
• Catalog.add_data_tap_guid
• Catalog.remove_data_tap

To view information about data taps that have been added, query catalog.execution_data_taps .

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 catalog.execution_data_statistics 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 catalog.execution_component_phases view.

The dm_execution_performance_counters 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.

• Catalog.delete_project
• Catalog.deploy_project
• Catalog.get_project
• Catalog.move_project
• Catalog.restore_project

These views provide details about packages, projects, and project versions.

• Catalog.projects
• Catalog.packages
• Catalog.object_versions

Parameters

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 catalog.set_object_parameter_value and catalog.clear_object_parameter_value . To set the value of a parameter for an instance of execution, call catalog.set_execution_parameter_value . You can retrieve default parameter values by calling catalog.get_parameter_values .

These views show the parameters for all packages and projects, and parameter values that are used for an instance of execution.

• Catalog.object_parameters
• Catalog.execution_parameter_values

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.

• Catalog.create_environment
• Catalog.delete_environment
• Catalog.move_environment
• Catalog.rename_environment
• Catalog.set_environment_property
• Catalog.create_environment_variable
• Catalog.delete_environment_variable
• Catalog.set_environment_variable_property
• Catalog.set_environment_variable_value

By calling the catalog.set_environment_variable_protection 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.

• Catalog.create_environment_reference
• Catalog.delete_environment_reference
• Catalog.set_environment_reference_type

For more details about environments and variables, query these views.

• Catalog.environments
• Catalog.environment_variables
• Catalog.environment_references

Executions and Validations

An execution is an instance of a package execution. Call catalog.create_execution and catalog.start_execution to create and start an execution. To stop an execution or a package/project validation, call catalog.stop_operation .

To cause a running package to pause and create a dump file, call the catalog.create_execution_dump 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.

• Catalog.executions
• Catalog.operations
• Catalog.operation_messages
• Catalog.extended_operation_info
• Catalog.event_messages
• Catalog.event_message_context

You can validate projects and packages by calling the catalog.validate_project and catalog.validate_package stored procedures. The catalog.validations 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.

Permissions

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.

• Catalog.deny_permission
• Catalog.grant_permission
• Catalog.revoke_permission

Query catalog.explicit_object_permissions for the permissions that are explicitly assigned to the user for securable objects. Query catalog.effective_object_permissions for the permissions that are assigned to the current database principal for securable catalog objects.

Folders

You can create, delete, and rename catalog folders, as well as set descriptions by calling the following stored procedures.

• Catalog.create_folder
• Catalog.delete_folder
• Catalog.rename_folder
• Catalog.set_folder_descriptions

You can query catalog.folders for a list of folders in the catalog.