Help Manual - Premium SQL Server Command Task

Documentation on the SSIS Premium SQL Server Command task component

Using the Premium SQL Server Command Task

The Premium SQL Server Command Task is a control flow component that can be used to run commands to a database instance directly at the control flow level. It can be used to execute queries, and also to get the output in a required manner. There are three pages that can be configured:

  • General
  • Output
  • Error Handling

General Page

The General page includes the general properties of the component.

Premium SQL Server Command Task

Connection Manager

The Connection Manager drop-down displays a list of all connection managers that are available for your current SSIS package.

Command Timeout

The Command Timeout specifies the seconds after which the command being executed would timeout. It is set by default to 15.

Transaction Type

This option allows you to specify the type of transactions by choosing Explicit and selecting an Isolation Level or by choosing Implicit.

Transaction Isolation Level

The Transaction Isolation Level option allows you to specify concurrency behaviors for SQL Server tables. The below Isolation levels are available.

  • Default
  • Chaos
  • Read Committed
  • Read Uncommitted
  • Repeatable Read
  • Serializable
  • Snapshot

Parameterize Command

Parameterizing commands protects against SQL injection. Only disable this option when it is required, and you have absolute control over the values being merged into the command.

Schemas...

The Command textbox is the command text that will be executed to perform an action against the connected Premium ADO.NET instance. You can easily create a command by dragging and dropping the table names and adding the keywords from the panel on the left side to the command editor window.

The Command textbox supports the use of User and System Variables. Simply select a variable under the SSIS Variables menu in the left-hand side panel, and a placeholder value will be inserted into the filter text.

Import...

Loads command from a file into the Command property.

Export...

Save the command in the Command property to a file.

Preview...

Opens a preview dialog that shows the result (up to the first 200 rows) of executing the text in the Command property over the specified Connection Manager.

Note: If the command makes any changes to the database, the changes will appear in the preview but are rolled back immediately. Changes to the database will only be commited at runtime.

Expression fx Button

Click the fx button to launch SSIS Expression Editor to enable dynamic updates of the property at run time.

Outputs Page

The Output page of the Premium SQL Server Command Task shows you the available output settings.

Premium SQL Server Command Task - Output

Output Type

The Output Type can be any of the options listed below:

  • None
  • Single Row
  • Scalar
  • Result Set
Single Row Selection Mode (Only for Single Row Output Type)

You could choose the Single Row Selection Mode from the options below:

  • First
  • Last
  • Specify Row Index
Single Row Index (Only for Single Row Output Type)

Specify the Single Row index value in this field.

Single Row Output Variables (Only for Single Row Output Type)

This grid can be used to set the Column Index and the Output Variables

  • Column Index: The index for the column to be read.
  • Output variable: The variable to which it should be written
Scalar Output Variable (Only for Scalar Output Type)

Choose the variable to store the Scalar output.

Result Set Variable (Only for Result Set Output Type)

Choose the variable to store the result set output. Usually, this is an Object variable.

Error Handling Page

The Error Handling page can be used to handle errors.

Premium SQL Server Command Task - Error Handling

Fail on error

When chosen, will fail the component on error.

Write error to variable

Will write the error message to a variable.

Ignore error

Ignores the error and continues the task.