Help Manual- Databricks Command Task

Documentation on SSIS Databricks Command Task component

Using the Databricks Command Task

The Databricks Command Task is a control flow component that can be used to run commands to Databricks 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.

Databricks Command Task

Connection Manager

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

Warehouse

The Warehouse option is a drop-down list that allows you to pick one of the Databricks warehouses available in your Databricks instance.

Parameterize Query

Parameterize Query 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.

Use External Link

Using EXTERNAL LINKS allows you to work with large result sets efficiently.

Command

The Command textbox is the command text that will be executed to perform an action against the connected Databricks 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 PrestoDB Command Task shows you the available output settings.

Databricks 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.

Databricks 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.