Stored Procedure

Executes the selected stored procedure via OLEDB on the datasource specified. Stored procedures are configured on the database server and are often faster than repeated SQL calls. Some key benefits include:

Precompiled execution - SQL Server compiles each stored procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when stored procedures are called repeatedly.
Reduced client/server traffic - If network bandwidth is a concern in your environment, you'll be happy to learn that stored procedures can reduce long SQL queries to a single line that is transmitted over the wire.
Efficient reuse of code and programming abstraction - Stored procedures can be used by multiple users and client programs. If you utilize them in a planned manner, you'll find the development cycle takes less time.
Enhanced security controls - You can grant users permission to execute a stored procedure independently of underlying table permissions.

Useful for automated retrieval, update and management of data. Could also be used to test database response times and size quotas. A working knowledge of SQL will help most people get the most out of this action.

Declaration

Example:

Execute the stored procedure: "EXECUTE sp_GetInventory 'FL'". The parameters are: "Name" (names), "var_1" (variables to hold the values), "String" (types), "Input" (directions), "Value" (values), "2" (max. number of characters that can appear to the right of the decimal place), "22" (max. number of digits allowed in the parameter), "2" (sizes), "Signed" (type of values accepted by the parameter).

Because the results retrieved may contain multiple records (rows) and multiple fields (columns) - to access data retrieved from SQL Query you must use the Loop Dataset action to iterate the records and embedded expressions in order to extract the data from the individual fields (i.e. %datasetname.fieldname%). See notes below for more information.

General Tab

Property	Type	Required	Default	Markup	Description
Connection String Type					Indicates where this action's connection string should originate from. The connection string includes the source database name, driver, username, password and other properties needed to establish the initial connection. Different parameters are available depending on the option selected. The available options are: Use custom connection string (Default) - Specifies that a custom OLEDB connection string will be set for this action. Select this option if performing a single query. Use existing SQL connection from a session - Specifies that a connection string should derive from a session created by a previous Open SQL Connection step. This allows several queries to be linked to a specific session. Use pre-defined connection string - Specifies that the connection should derive from a variable or constant.
Connection String	Text	Yes if custom connection string is selected	(Empty)	CONNECTIONSTRING= "Encrypted_data"	Specifies an OLEDB connection string. Click Data Link Properties to open the Data Link Properties Wizard allowing you to setup a custom connection string. Valid only if Use custom connection string is selected in the Connection String Type parameter. For more details on setting, see Data_Link_Properties_Wizard.
Session Name	Text	Yes if connection string originates from a session	SQLSession1	SESSION="Session10"	Indicates the session name that this connection should derive from. Valid only if Use existing SQL connection from a session is selected in the Connection String Type parameter.
Pre-defined Connection String	Text	Yes if pre-defined connection string is selected	(Empty)	PREDEFINEDCONNECTION="connection"	Indicates the pre-defined connection string that should be used. Valid only if Use pre-defined connection string is selected in the Connection String Type parameter.
Prompt user for a name and password	Yes/No	No	No	LOGIN="YES"	Specifies that the user should be prompted to enter a username and password for the database server each time the task is run. Valid only if Use custom connection string or Use pre-defined connection string is selected in the Connection String Type parameter.
Stored Procedure	Text	Yes	(Empty)	PROCEDURE="EXECUTE sp_GetInventory 'FL'"	Specifies the stored procedure to execute.
Parameters	Text	No	(Empty)	NAMES="Name" VARIABLES="var_1" TYPES="String" DIRECTIONS="Input" VALUES="Value" PRECISIONS="2" NUMSCALES="22" SIZES="2" ATTRIBUTES="Signed"	Specifies the parameters of the stored procedure to execute. Click Add to add parameters.

Advanced Tab

Property

Type

Required

Default

Markup

Description

The query times out after (optional)

Number

(Empty)

TIMEOUT="2"

MEASURE="minutes"

Specifies the amount of time that the query should be allowed before timing out at the server level. Enter a value and select from the following time intervals:

milliseconds
seconds (default)
minutes
hours

Create and populate dataset (optional)

Text

(Empty)

RESULTDATASET="theDataset"

Indicates the name of the dataset that should be created and populated with the results (if any) of the stored procedure upon execution. To access the data in subsequent steps, specify %datasetname.fieldname% (where datasetname specifies the name of the dataset and fieldname specifies the name of the dataset field) inside a Loop Dataset action (to recurse the rows).

Description Tab

The Description tab allows you to customize the text description of any step as it appears in the Task Builder's Steps Pane.

Error Causes Tab

The Error Causes tab allows you to select/omit specific errors that should cause a particular step to fail.

On Error Tab

The On Error tab allows you to determine what the task should do if a particular step encounters an error.

More about On Error properties

Variables and Expressions

All text fields allow the use of expressions, which can be entered by surrounding the expression in percentage signs (Example: %myVariable% or % Left('Text',2)%). To help construct these expressions, you can open Expression Builder from these fields by clicking the percent sign (%) or pressing F2.