Using Data Import Command

 

1 About Data Import Command

In SQLMessenger, you can use the Data Import Command to import data from an Excel file into a specified database table when the task is starting. The task will continue to execute after the data has been successfully imported.

For example, you can place the data to be processed in an Excel spreadsheet. When executing the task, use the Data Import Command to import this data into a statistics table in the database first, and then perform statistical analysis on this data within the task to generate reports.

Import data into sales_stat table in the database

Note: If a task is configured with a Data Import Command, instances started automatically by the Task Schedule of the task will end in failure. You must manually execute this task and select the Excel file to be imported for the task instance.

Automatically started task instances will end in failure

2 Create Data Import Command

You can click the New Command button on the Task Commands toolbar to open the Create New Command dialog box. In the dialog box, select the command type "Create a Data Import Command".

Select the command type "Create a Data Import Command" to create

Create new excel data import command

In this dialog box:

Command Name	Enter a name fro the command (optional).
Template File	Select a Excel template for for the command. The first row of the first sheet in the template file contains the names of the columns to be imported. The template file does not need to contain data. Excel template file
Target Database	Select the data source where the target table for data import is located.
Target Table	Enter the name of the target table. The target table must already exist. Once the entry is complete, the system will automatically read the field list of the target table.
Field List	Here you can configure which columns from the source file should be imported into which fields of the target table.

3 Setting Value Sources for Target Table Fields

You need to set the value sources for the fields of the target table in the Field List. The value source for each field can be a column from an Excel template table, a variable, or a fixed value.

Set the value source for the field of target table

In this dialog box, you can select a source column for the field of the target table. When the command executes, the system will import data from the corresponding column of the source data file into the field of the target table.

Select a source column as the value source for the field

You can also use a variable (such as @@TaskInstId, it returns the unique ID of the task instance) as the value source for the field. When the command executes, the system will populate the field in the target table with the value of the specified variable.

Select a variable as the value source for the field

Using Variables in Tasks

In addition, you can also use a fixed value as the value source for the field. When the command executes, the system will populate the field in the target table with the fixed value you have entered.

Enter a fixed value as the value source for the field

4 Advanced Options

Advanced Options

Batch insert into target table	If this option is selected, the command will execute batch INSERT statements to insert the source data into the target table. This can improve the speed of inserting data.
Use a separate database connection to connect to the target database	If this option is selected, the command will connect to the target data source separately. After importing the data, it will automatically execute a COMMIT statement to commit the imported data. Otherwise, the command will share the connection to the target data source with the task instance. After importing the data, it will not automatically commit the imported data, and you will need to explicitly use a COMMIT statement in a subsequent Task Command to commit the data. Using the COMMIT Statement to Commit Changes, DataSource Management
Automatically execute COMMIT operation to commit the imported data after data import is completed	If this option is selected and "Use a separate database connection to connect to the target database" is not chosen, the imported data will be committed immediately after the command execution completes. Any other SQL commands in this task instance that have modified data using the same data source connection will also be committed together.