Command Line SQL Statement Execution Tool Overview

DTM ODBC SQL runner is a free command line tool that executes SQL statement and saves results to text (TSV, CSV, etc.) files. The professional edition with extended functionality and better performance is also available.
The tool is most suitable for batch and scheduled execution. It is a Windows application (32 and 64 bit) that supports Windows 7 and newer, Windows Server 2008 and newer.

Download 32 bit and 64 bit. No installation is required.

The tool can be tuned by configuration file and a few command line parameters. The specified command line parameter overrides the value in the configuration file.
Important: even you have a free edition of the tool your license file (default name is "license.lic") should be located in the folder with executable file.

Configuration File

The configuration is a text file. By default, the configuration file name is "robot.drp" and it can be changed by '-config' command line switch.
The configuration starts with "Step" clause and has four sections: Source, Target, Transform^*, Settings.
Each value in the section takes a line and seems like name=value. ';' means begin of the comment. You can find sample configuration file "robot.drp" in the installation package.

Important: you should quote by " any value with ';' inside, example: ConnectString="Driver={SQL Server Native Client 11.0};Server=.;Database=Northwind;Trusted_Connection=yes;"

^* - option is available in the professional version only.

Source Section

Type=DatabaseQuery This parameter defines type of the source data. The user can't change it.

DSN=<string> System or user ODBC Data Source Name. Run Windows ODBC Administrator to define or modify it.

Login=<string> User name or login. Optional.

Password=<string> Password. Optional.

ConnectString^*="<string>" Complete connect string with all driver-depended parameters. 'DSN', 'Login' and 'Password' are not compatible with this option.

Query=<string> SQL Statement(s). The professional version supports $DATE$ and $TIME$ macros in the query text. Not compatible with 'QueryFile' option.

QueryFile=<file name> File name with SQL Statement(s). Not compatible with 'Query' option.

Target Section

Type=TextFile This parameter defines type of the target. The user can't change it.

Name=<file name> The file name where the results should be saved. The professional version supports $DATE$ and $TIME$ macros in the name.

Encoding=ANSI or UTF8^* or Unicode^* Output file encoding. Default is ANSI.

Header=1 O means no line with column names.

Delimiter=tab Column delimiter: tab, ",", "|", etc.

Overwrite=1 O means append data to the end of existing file.

NullIsEmptyString=0 1 means NULL values will be written as empty string, not 'NULL' string.

FixedWidth^*=0 1 means fixed with file format.

QuotationMode=Never, ByValue or Always Never is default value. 'ByValue' means quote only values with delimiter inside.

QuoteChar=" Symbol for value quoting.

EndOfLine=CRLF or LF CRLF is default.

Transform^* Section

This section is optional. The user is enabled to define a few transformation sections in the step.

Type=one of 'RemoveColumn', 'RenameColumn' or 'ReplaceString' defines type of the transformation.

ApplyTo=<index> Index (the first is 1) of column or * if applicable (means apply to all columns).

NewName=<column name> new column name for 'RenameColumn' transformation.

From=<string> Source substring for 'ReplaceString' transformation.

To=<string> Target substring or empty for removing for 'ReplaceString' transformation.

Settings Section

Log=<file name> Write log file. By default any messages will be written to the console.

Overwrite=0 1 means recreate a log before each execution of the tool.

DateMacroFormat^*=YYYY-MM-DD $DATE$ macro format.

TimeMacroFormat^*=HHMMSS $TIME$ macro format.

ShowProgress=<number> Default is 0 (means disabled). Show '.' in console for each number of processed rows.

Variables*

Variable is a named value that can be passed to software as command line parameter.
Typically, it has $name$ format. The program will replace variable to it's value in the following contexts: file names, connect strings, table names.

Command Line Switches

-config=<file name> Custom configuration file name. "robot.drp" in the current directory will be used by default.

-license=<file name> Custom license file name. "license.lic" in the current directory will be used by default.

-silent^* Do not write anything to the system console. Suitable for calling from service.

-Log=<file name> Write log file. By default any messages will be written to the console.

-Q Show actual parameters in the console.

-Type=DatabaseQueryToTextFile This parameter defines type of the operation. The user can't change it.

-DSN=<string> System or user ODBC Data Source Name. Run ODBC Administrator to define or modify it.

-Login=<string> User name or login.

-Password=<string> Password.

-Script=<file name> Same as "QueryFile" parameter of the "Source" section.

-Header=<0 or 1> See Header of the target section.

-Output=<file name> Same as "Name" parameter of the "Target" section.

-LogName=<file name> Same as "Log" parameter of the "Settings" section.