Stored Procedures (Integration Services Catalog)

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.cleanup_server_log
  Cleans up operation logs to bring the SSISDB database into a state that lets you change the value of the SERVER_OPERATION_ENCRYPTION_LEVEL property.

• catalog.cleanup_server_execution_keys
  Drops certificates and symmetric keys from the SSISDB database.