SSIS T-SQL API Overview

Copper Contributor

Mar 25, 2019

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 http://msdn.microsoft.com/en-us/library/ff878042(SQL.110).aspx stored procedure and query the http://msdn.microsoft.com/en-us/library/ff878147(SQL.110).aspx 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. .

http://msdn.microsoft.com/en-us/library/hh230989(SQL.110).aspx
http://msdn.microsoft.com/en-us/library/hh230991(SQL.110).aspx
http://msdn.microsoft.com/en-us/library/hh230990(SQL.110).aspx

To view information about data taps that have been added, query http://msdn.microsoft.com/en-us/library/hh230985(SQL.110).aspx .

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 http://msdn.microsoft.com/en-us/library/hh230986(SQL.110).aspx 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 http://msdn.microsoft.com/en-us/library/hh230981(SQL.110).aspx view.

The http://msdn.microsoft.com/en-us/library/hh230983(SQL.110).aspx 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.

http://msdn.microsoft.com/en-us/library/ff878158(SQL.110).aspx
http://msdn.microsoft.com/en-us/library/ff878004(SQL.110).aspx
http://msdn.microsoft.com/en-us/library/ff878156(SQL.110).aspx
http://msdn.microsoft.com/en-us/library/ff878153(SQL.110).aspx
http://msdn.microsoft.com/en-us/library/ff878092(SQL.110).aspx

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

http://msdn.microsoft.com/en-us/library/ff878098(SQL.110).aspx
http://msdn.microsoft.com/en-us/library/ff878097(SQL.110).aspx
http://msdn.microsoft.com/en-us/library/ff878005(SQL.110).aspx

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 http://msdn.microsoft.com/en-us/library/ff878162(SQL.110).aspx and http://msdn.microsoft.com/en-us/library/ff878144(SQL.110).aspx . To set the value of a parameter for an instance of execution, call http://msdn.microsoft.com/en-us/library/ff877990(SQL.110).aspx . You can retrieve default parameter values by calling http://msdn.microsoft.com/en-us/library/ff878039(SQL.110).aspx .

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

http://msdn.microsoft.com/en-us/library/ff878140(SQL.110).aspx
http://msdn.microsoft.com/en-us/library/ff878152(SQL.110).aspx

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.

http://msdn.microsoft.com/en-us/library/ff878040(SQL.110).aspx
http://msdn.microsoft.com/en-us/library/ff878137(SQL.110).aspx
http://msdn.microsoft.com/en-us/library/ff878100(SQL.110).aspx
http://msdn.microsoft.com/en-us/library/ff878127(SQL.110).aspx
http://msdn.microsoft.com/en-us/library/ff878096(SQL.110).aspx
http://msdn.microsoft.com/en-us/library/ff878093(SQL.110).aspx
http://msdn.microsoft.com/en-us/library/ff878090(SQL.110).aspx
http://msdn.microsoft.com/en-us/library/ff878125(SQL.110).aspx
http://msdn.microsoft.com/en-us/library/ff877996(SQL.110).aspx

By calling the http://msdn.microsoft.com/en-us/library/ff877989(SQL.110).aspx 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.

http://msdn.microsoft.com/en-us/library/ff878036(SQL.110).aspx
http://msdn.microsoft.com/en-us/library/ff877997(SQL.110).aspx
http://msdn.microsoft.com/en-us/library/ff878101(SQL.110).aspx

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

http://msdn.microsoft.com/en-us/library/ff878041(SQL.110).aspx
http://msdn.microsoft.com/en-us/library/ff878035(SQL.110).aspx
http://msdn.microsoft.com/en-us/library/ff878154(SQL.110).aspx

Executions and Validations

An execution is an instance of a package execution. Call http://msdn.microsoft.com/en-us/library/ff878034(SQL.110).aspx and http://msdn.microsoft.com/en-us/library/ff878160(SQL.110).aspx to create and start an execution. To stop an execution or a package/project validation, call http://msdn.microsoft.com/en-us/library/ff878095(SQL.110).aspx .

To cause a running package to pause and create a dump file, call the http://msdn.microsoft.com/en-us/library/hh230987(SQL.110).aspx 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.

http://msdn.microsoft.com/en-us/library/ff878089(SQL.110).aspx
http://msdn.microsoft.com/en-us/library/ff878094(SQL.110).aspx
http://msdn.microsoft.com/en-us/library/ff877994(SQL.110).aspx
http://msdn.microsoft.com/en-us/library/ff878141(SQL.110).aspx
Catalog.event_messages
Catalog.event_message_context

You can validate projects and packages by calling the http://msdn.microsoft.com/en-us/library/ff878038(SQL.110).aspx and http://msdn.microsoft.com/en-us/library/ff877995(SQL.110).aspx stored procedures. The http://msdn.microsoft.com/en-us/library/ff878143(SQL.110).aspx 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.

http://msdn.microsoft.com/en-us/library/ff878146(SQL.110).aspx
http://msdn.microsoft.com/en-us/library/ff878150(SQL.110).aspx
http://msdn.microsoft.com/en-us/library/ff878044(SQL.110).aspx

Query http://msdn.microsoft.com/en-us/library/ff878037(SQL.110).aspx for the permissions that are explicitly assigned to the user for securable objects. Query http://msdn.microsoft.com/en-us/library/ff878149(SQL.110).aspx 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.