The SQL transformation connects to a relational database to run the stored procedure. The SQL transformation can call stored procedures from Oracle, IBM DB2, Microsoft SQL Server, Sybase, and ODBC. A stored procedure is kept in the database, and it runs in the database.

Create an ODBC connection to call a stored procedure from a Sybase database. You must also create an ODBC connection to call a stored procedure from a Microsoft SQL Server database on non-Windows operating systems.

A stored procedure is a pre-compiled collection of Transact-SQL, PL-SQL, or other database procedural statements. Stored procedure syntax varies based on the database.

You might use stored procedures to complete the following tasks:

Check the status of a target database before loading data into it.
Determine if enough space exists in a database.
Perform a specialized calculation.
Retrieve data by a value.
Drop and re-create indexes.

You can use a stored procedure to perform a query or calculation that you would otherwise include in a transformation. For example, if you have a well-tested stored procedure for calculating sales tax, you can perform that calculation with the stored procedure instead of recreating the same calculation in an Expression transformation.

A stored procedure can accept input and then return a result set of rows. A stored procedure can run a DDL task that requires no input and then returns no output.

You can configure the SQL transformation to run more than one stored procedure. For each stored procedure that you configure, configure transformation ports to match the stored procedure parameters. Each stored procedure can pass data back to output ports.

The database that contains the stored procedure has user permissions. You must have permissions to run the stored procedure on the database.