Help Manual - Premium ADO.NET Command Task

Documentation on the SSIS Premium ADO.NET Command task component

Using the Premium ADO.NET Command Task

The Premium ADO.NET 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 for the component.

Premium ADO.NET Command Task

Connection Manager

The Connection Manager drop-down displays a list of all connection managers that are available for your current SSIS package. The component currently supports the following data providers:

  • SQL Client Data Provider
  • ODBC Data Provider
  • OLE DB Data Providers
  • Oracle Client Data Provider
  • ODP.NET Managed and Unmanaged
  • MySQL Client Data Provider
  • PostgreSQL Data Provider
  • Db2 Data Provider
  • Firebird Data Provider
  • Sybase Data Provider
  • SQLite Data Provider
Command Timeout

The Command Timeout specifies the seconds after which the command being executed would timeout.

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 (Available only when "Explicit" Transaction Type is selected)

The Transaction Isolation Level option allows you to specify concurrency behaviors for Premium ADO.NET 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.

Command

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 commit 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 ADO.NET Command Task shows you the available output settings.

Premium ADO.NET Command Task - Output

Output Type

The Output Type can be any of the 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 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)

The 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 ADO.NET 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.