> For the complete documentation index, see [llms.txt](https://dbi-services.gitbook.io/omrun-help/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://dbi-services.gitbook.io/omrun-help/element-details/test-program-object/data-staging.md).

# Data Staging

## ![](/files/ZmGkX6hu7XyzkY2aPSnI)

## 1. Abstract

The data staging plug-in was introduced with release 4.2.1.0 and supports MS SQL-Server databases (OLEDB) for data staging only. It was enhanced with the release 5.6.x.x by using the schema.ini definition. Plug-in is able to import data from 2 different data sources to a Microsoft SQL-Server staging database:

* Import of **all latest result files (TOR)** from an OMrun component:\
  Each latest test result for every object within the selected component path are loaded into the staging database.
* Import of **all CSV flatfiles** from a directory:\
  All csv flatfiles within the selected directory path are loaded into the staging database.

To connect to the staging database, the credentials from the OMrun environment definition are used and transferred via "Data Link" drop down menu to the plug-in. During the load process the system generates automatically staging queries (see input field `@param2` below).

### Staging Types

The plug-in supports two type of staging mechanism:

* **append** (Default, appends test result to an existing or new data table in the staging database)
* **delete** (deletes data table content in the staging database and imports the new results into an existing or new data table)

The test results are automatically loaded with the database schema dbo. In case there is a need for another database schema, it can be overwritten by a parameter (see input field `@param3` below).

### Staging Definition Settings

In the data staging configuration file there are some base definition settings for further adjustments:

* **SchemaName** (Standard schema name definition)
* **DropTable** (Truncate / drop data table)

After running the data staging process for every test data object within the addressed directory (with TOR or CSV file content) there exists a data table on the staging database with the imported records from all selected files.

***

## 2. Input

Execution parameters to invoke a data staging:

### Drop down settings

* Program Call: DataStaging
* Data Link: \<DB variable>

### Parameter List settings

**Field `@param1`** (Component path) \[mandatory]

* Description: OMrun component path location (for TOR files) or directory path with CSV flatfiles, no file name or extension. Alternatively, a full path to a **single file** (`.csv` or `.tor`) can be provided to load only that specific file.
* Syntax: \<full path> or \<full path to single file>
* Example directory: `"C:\Data\TestScripts\Project\DataPrep"`
* Example single file: `"C:\Data\TestScripts\Project\DataPrep\result.csv"`

**Field `@param2`** (Query path) \[optional]

* Description: Query path, if defined, which will contain all auto-generated query files for the loaded test results after data staging process.
* Syntax:
  * \<empty> = no queries generated
  * \<full path for query files>
* Example: `"C:\Data\TestQueries"`

**Field `@param3`** (Schema name) \[optional]

* Description: Schema name, overwrites the default setting from config file (default = dbo)
* Syntax:
  * \<empty> = uses "dbo" as the database schema name
  * \<Schema name>
* Example: `stage`

**Field `@param4`** (Load type) \[optional]

* Description: Defines how new test results are loaded into database tables
* Syntax:
  * \<empty> = appends result data (same as "append")
  * \<append> = appends new results to existing data
  * \<delete> = deletes result data before loading new results
* Example 1: `append`
* Example 2: `delete`

> :warning: **Attention**\
> The methode "append" does not work if configuration file parameter has been set to "DropTable = true".

#### System parameter

Parameter 5 and 6 are transmited to data stage automatically. The user cannot set this parameters, they are system based and depend on the selected database or the execution mode (batch/gui)

**Field `@param5`** (Environment) \[mandatory]

* Description: OMrun environment name used to select the correct TOR result files for staging. If empty or not resolvable, the staging process will abort with an infrastructure error.
* Syntax: \<environment name>
* Example: `DEV`

**Field `@param6`** (Batch mode) \[mandatory]

* Description: Indicates whether the plug-in is running in automated batch mode. When set to `true`, the GUI wait delay (500 ms) at process end is skipped, allowing for faster execution in scheduled or pipeline runs.
* Syntax:
  * \<empty> or `false` = normal (interactive) mode
  * `true` = batch mode, no GUI wait
* Example: `true`

### exclude.ini (CSV mode only)

When loading CSV files from a directory, the plug-in checks for an optional `exclude.ini` file in the source directory. Any CSV filename listed in that file (one entry per line) is skipped during the staging process and a warning is written to the log.

* Path: `<@param1 directory>\exclude.ini`
* Example content:

```
# Exclude certain files from being imported
exclude_file_1.csv
exclude_file_2.csv
```

### Example

<figure><img src="/files/mvgbu1PPpjibqfl3kMxi" alt="OMrun Object: Data Staging"><figcaption><p>OMrun Object: Data Staging</p></figcaption></figure>

***

## 3. Configuration File

The application configuration file contains six additional parameters to adjust the general behavior of the plug-in.\
**Path:** `C:\Program Files\dbi\OMrun\BuiltInTests\OMrunPlugIn_DataStaging.dll.config`

### Parameter SchemaName (1)

This setting contains the standard database schema name definition used in case there is no "@param3" defined.

### Parameter DropTable (2)

Function to truncate data table or to drop data table during load process.

DropTable = "false" (default)

* `@param4` = delete\
  **Truncates** the data table before loading new data, creates a new table if not exists or modify data table structure if necessary.
* `@param4` = append\
  **Appends** records to the existing data table, creates a new table if not exists or modifyies data table structure if necessary.

DropTable = "true"

* `@param4` = delete\
  The staging table will be dropped and re-created before the result data is loaded.
* `@param4` = append (**not supported**) :warning:\
  Function "append" data with config parameter DropTable = "true" is not allowed.

### Parameter IncludeScenarioResults (3)

Function to load test scenario results in addition to there test object results.

IncludeScenarioResults = "false" (Default)

### Parameter LatestResult (4)

Function to define if all results within a directory (scenario, environment) are loaded or the latest result only.

LatestResult = "true" (Default)

### Parameter StandardLimitCommit (5)

Function to define after how many imported data rows a commit to the database is executed. This parameter enables the user to optimize import performance for his/her database. The standard setting commits staged data after every 100'000 data rows. The maximal amount of rows possible to import before a commit to the database are 2'147'483'647 rows.

StandardLimitCommit = 100000 (Default)

### Parameter StandardCsvDelimiter (6)

Function to define the delimiter used as default value if it is not defined otherwise within schema.ini file.

StandardCsvDelimiter = ";" (Default)

## 4. schema.ini

A `schema.ini` file placed in the same directory as the CSV files can be used to control how individual files are parsed. Each CSV file may have a matching `[filename.csv]` section with one or more of the following keys.

### Supported section keys

| Key                    | Description                                                     | Example                                                                       |
| ---------------------- | --------------------------------------------------------------- | ----------------------------------------------------------------------------- |
| `Format`               | Delimiter type                                                  | `CSVDelimited` / `TabDelimited` / `Delimited(;)`                              |
| `CharacterSet`         | File encoding by name                                           | `UTF8` / `UTF7` / `UTF32` / `ASCII` / `BigEndianUnicode` / `ANSI` / `Unicode` |
| `Character`            | File encoding by Windows code-page number                       | `65001`                                                                       |
| `ColNameHeader`        | Whether the first row contains column names                     | `True` / `False`                                                              |
| `DateTimeFormat`       | Input format for datetime columns (tried before auto-detection) | `yyyy-MM-dd HH:mm:ss.fff`                                                     |
| `DateFormat`           | Input format for date-only columns                              | `yyyy-MM-dd`                                                                  |
| `DateTimeOffsetFormat` | Input format for datetimeoffset columns                         | `yyyy-MM-dd HH:mm:ss.fffffff zzz`                                             |

### Column definitions

Individual columns can be declared explicitly to enforce a specific data type:

**Excel type tokens**

| Token      | Description                       |
| ---------- | --------------------------------- |
| `Long`     | 32-bit integer                    |
| `Text (N)` | Fixed-length text (specify width) |
| `Memo`     | Variable-length text              |
| `Bit`      | Boolean (0/1)                     |
| `Integer`  | 16-bit integer                    |
| `Single`   | Single-precision float            |
| `Double`   | Double-precision float            |
| `Currency` | Currency value                    |
| `Decimal`  | Exact numeric                     |
| `DateTime` | Date and time                     |
| `Byte`     | 8-bit unsigned integer            |

**MS SQL type tokens**

| Token               | Description                              |
| ------------------- | ---------------------------------------- |
| `bigint`            | 64-bit integer                           |
| `binary(N)`         | Fixed-length binary                      |
| `bit`               | Boolean                                  |
| `char(N)`           | Fixed-length ANSI string                 |
| `date`              | Date only                                |
| `datetime`          | Date and time                            |
| `datetime2(N)`      | High-precision date and time             |
| `datetimeoffset(N)` | Date, time and timezone offset           |
| `decimal(p,s)`      | Exact numeric with precision and scale   |
| `float`             | Double-precision floating point          |
| `image`             | Variable-length binary (legacy)          |
| `int`               | 32-bit integer                           |
| `money`             | Monetary value                           |
| `nchar(N)`          | Fixed-length Unicode string              |
| `ntext`             | Variable-length Unicode text (legacy)    |
| `numeric(p,s)`      | Exact numeric (synonym for decimal)      |
| `nvarchar(N)`       | Variable-length Unicode string           |
| `nvarchar(MAX)`     | Unlimited variable-length Unicode string |
| `real`              | Single-precision floating point          |
| `smalldatetime`     | Date and time (lower precision)          |
| `smallint`          | 16-bit integer                           |
| `smallmoney`        | Small monetary value                     |
| `sql_variant`       | Stores values of various SQL types       |
| `text`              | Variable-length ANSI text (legacy)       |
| `time(N)`           | Time only                                |
| `timestamp`         | Row version stamp                        |
| `tinyint`           | 8-bit unsigned integer                   |
| `uniqueidentifier`  | GUID                                     |
| `varbinary(N)`      | Variable-length binary                   |
| `varbinary(MAX)`    | Unlimited variable-length binary         |
| `varchar(N)`        | Variable-length ANSI string              |
| `varchar(MAX)`      | Unlimited variable-length ANSI string    |
| `xml`               | XML data                                 |

> Geography, Geometry, and HierarchyId columns should be mapped to `nvarchar(N)` as they have no direct flat-file equivalent.

**Example schema.ini (MS SQL types)**

***

## 5. Output

### Table Name

The staged result files are generally named as the test object name from OMrun:

* Table name "schema.TestObjectName\_SequenceNumber" if parameter list "Extensive" was selected
* Table name "schema.TestObjectName\_SequenceNumber\_ParameterListName" if a different parameter list as "Extensive" was selected.

### Table Structure

During the load process the table structure is defined related to the loaded data and is automatically modified if the data format range is not sufficient enough or an additional data column has been detected.

With a schema.ini file in the upload folder, the the auto load function is overwritten and the csv input data is chacked against the specified column definition.

An existing data column is never automatically deleted (the only exception is: parameter DropTable in application configuration file is set to "true").

### Data Load Results

During the load process a log file is created that contains any issue detected during the load process: A successful data load process execution is stored as a 'passed' result in OMrun.

Each staging run is assigned a unique **Run GUID** that is printed to the console output and written to the log file at the start of every execution. This GUID can be used to correlate log entries across multiple concurrent or sequential runs.