SQL Command - 23.1

Select the database connection you want to use. Your choices vary depending on what connections are defined in the Connection Manager of Spectrum Management Console. If you need to make a new database connection, or modify or delete an existing database connection, click Manage Connections.

Enter the SQL statements you want to run for each record in the dataflow. As you begin to type, an auto-complete pop-up window will display the valid SQL commands. Separate multiple SQL statements with a semicolon (;).

To specify a value from a dataflow field, use this syntax:

${<field name>}

Where <field name> is the name of a field in the data flow.

For example,

UPDATE MyDatabase.dbo.customer
SET name=${Name}
WHERE id=${ID};

In this example ${Name} will be replaced with the value from the dataflow's Name field and ${ID} will be replaced with the value from the dataflow's ID field.

Specifies whether to process records in batches or to process all records at the same time. One of these:

Batch size

Groups records into batches of the size you specify and processes one batch at a time.

Entire Run

Creates one large batch for all records and processes all transactions at the same time.

Type one or more SQL statements that you want to execute before the records coming into the stage are processed. The SQL statements you enter here are executed once per run after the data flow starts running but before the SQL Command stage processes the first records.

An example use of pre-SQL would be to create a table for the records that will be processed.

Check this box to commit the pre-SQL statements before executing the SQL statements on the General tab.

If you do not check this box, the pre-SQL statements will be committed in the same transaction as the SQL statements on the General tab.

Type one or more SQL statements that you want to execute after all the records are processed. The SQL statements you enter here are executed once per run after the SQL Command stage is finished but before the data flow finishes.

An example use of pre-SQL would be to build an index after processing the records.