Stored Procedures (Integration Services Catalog)

Applies to: SQL Server SSIS Integration Runtime in Azure Data Factory

Applies to: SQL Server

This section describes the Transact-SQL stored procedures that are available for administering Integration Services projects that have been deployed to an instance of SQL Server.

Call the Integration Services stored procedures to add, remove, modify, or execute objects that are stored in the SSISDB catalog.

The default name of the catalog is SSISDB. The objects that are stored in the catalog include projects, packages, parameters, environments, and operational history.

You can use the database views and stored procedures directly, or write custom code that calls the managed API. Management Studio and the managed API query the views and call the stored procedures that are described in this section to perform many of their tasks.

In This Section

catalog.add_data_tap
Adds a data tap on the output of a component in a package data flow.

catalog.add_data_tap_by_guid
Adds a data tap to a specific data flow path in a package data flow.

catalog.check_schema_version
Determines whether the SSISDB catalog schema and the Integration Services binaries (ISServerExec and SQLCLR assembly) are compatible.

catalog.clear_object_parameter_value (SSISDB Database)
Clears the value of a parameter for an existing Integration Services project or package that is stored on the server.

catalog.configure_catalog (SSISDB Database)
Configures the Integration Services catalog by setting a catalog property to a specified value.

catalog.create_environment (SSISDB Database)
Creates an environment in the Integration Services catalog.

catalog.create_environment_reference (SSISDB Database)
Creates an environment reference for a project in the Integration Services catalog.

catalog.create_environment_variable (SSISDB Database)
Create an environment variable in the Integration Services catalog.

catalog.create_execution (SSISDB Database)
Creates an instance of execution in the Integration Services catalog.

catalog.create_execution_dump
Causes a running package to pause and create a dump file.

catalog.create_folder (SSISDB Database)
Creates a folder in the Integration Services catalog.

catalog.delete_environment (SSISDB Database)
Deletes an environment from a folder in the Integration Services catalog.

catalog.delete_environment_reference (SSISDB Database)
Deletes an environment reference from a project in the Integration Services catalog.

catalog.delete_environment_variable (SSISDB Database)
Deletes an environment variable from an environment in the Integration Services catalog.

catalog.delete_folder (SSISDB Database)
Deletes a folder from the Integration Services catalog.

catalog.delete_project (SSISDB Database)
Deletes an existing project from a folder in the Integration Services catalog.

catalog.deny_permission (SSISDB Database)
Denies a permission on a securable object in the Integration Services catalog.

catalog.deploy_project (SSISDB Database)
Deploys a project to a folder in the Integration Services catalog or updates an existing project that has been deployed previously.

catalog.get_parameter_values (SSISDB Database)
Resolves and retrieves the default parameter values from a project and corresponding packages in the Integration Services catalog.

catalog.get_project (SSISDB Database)
Retrieves the properties of an existing project in the Integration Services catalog.

catalog.grant_permission (SSISDB Database)
Grants a permission on a securable object in the Integration Services catalog.

catalog.move_environment (SSISDB Database)
Moves an environment from one folder to another within the Integration Services catalog.

catalog.move_project ((SSISDB Database)
Moves a project from one folder to another within the Integration Services catalog.

catalog.remove_data_tap
Removes a data tap from a component output that is in an execution.

catalog.rename_environment (SSISDB Database)
Renames an environment in the Integration Services catalog.

catalog.rename_folder (SSISDB Database)
Renames a folder in the Integration Services catalog.

catalog.restore_project (SSISDB Database)
Restores a project in the Integration Services catalog to a previous version.

catalog.revoke_permission (SSISDB Database)
Revokes a permission on a securable object in the Integration Services catalog.

catalog.set_environment_property (SSISDB Database)
Sets the property of an environment in the Integration Services catalog.

catalog.set_environment_reference_type (SSISDB Database)
Sets the reference type and environment name associated with an existing environment reference for a project in the Integration Services catalog.

catalog.set_environment_variable_property (SSISDB Database)
Sets the property of an environment variable in the Integration Services catalog.

catalog.set_environment_variable_protection (SSISDB Database)
Sets the sensitivity bit of an environment variable in the Integration Services catalog.

catalog.set_environment_variable_value (SSISDB Database)
Sets the value of an environment variable in the Integration Services catalog.

catalog.set_execution_parameter_value (SSISDB Database)
Sets the value of a parameter for an instance of execution in the Integration Services catalog.

catalog.set_execution_property_override_value
Sets the value of a property for an instance of execution in the Integration Services catalog.

catalog.set_folder_description (SSISDB Database)
Sets the description of a folder in the Integration Services catalog.

catalog.set_object_parameter_value (SSISDB Database)
Sets the value of a parameter in the Integration Services catalog. Associates the value to an environment variable or assigns a literal value that will be used by default if no other values are assigned.

catalog.start_execution (SSISDB Database)
Starts an instance of execution in the Integration Services catalog.

catalog.startup
Performs maintenance of the state of operations for the SSISDB catalog.

catalog.stop_operation (SSISDB Database)
Stops a validation or instance of execution in the Integration Services catalog.

catalog.validate_package (SSISDB Database)
Asynchronously validates a package in the Integration Services catalog.

catalog.validate_project (SSISDB Database)
Asynchronously validates a project in the Integration Services catalog.

catalog.add_execution_worker (SSISDB Database)
Adds a Integration Services Scale Out Worker to an instance of execution in Scale Out.

catalog.enable_worker_agent (SSISDB Database)
Enable a Scale Out Worker for Scale Out Master working with this Integration Services catalog.

catalog.disable_worker_agent (SSISDB Database)
Disable a Scale Out Worker for Scale Out Master working with this Integration Services catalog.