How to use the RpETL web administration page to configure, run, and monitor ETL jobs.

Use this document if you work with the ETL through the browser UI and need to:

- create or update an ETL definition,
- choose source tables or custom queries,
- schedule recurring runs,
- run a job manually,
- review counts, logs, errors, and recent performance.

This guide focuses on the ETL web page, not the Windows service or command-line execution.

## What RpETL Does

RpETL copies data from a supported source system into a SQL Server destination and keeps the destination synchronized over time.

Typical source types include:

- Oracle
- SQL Server
- MySQL
- PostgreSQL
- Snowflake
- Microsoft Access
- DuckDB folder or file-backed sources such as CSV, TSV, JSON, Excel, Parquet, and XML

Destination support in this UI is SQL Server.

## Typical Workflow

1. Open an existing ETL or create a new one.
2. Select the source and destination connections.
3. Refresh the list of available tables.
4. Select the tables to load, or add custom queries.
5. Review table-level settings under Selected.
6. Configure global Options, Scheduler, and Notifications.
7. Save the ETL definition.
8. Run the ETL manually or wait for the scheduler.
9. Monitor Status, Logs, Errors, Jobs, Stats, and Counts.

## Page Overview

The ETL page is organized into a toolbar and a set of tabs.

### Toolbar

Depending on whether the ETL has already been saved, you may see these buttons:

- `Run`: starts the ETL immediately.
- `View`: opens the report or view page tied to the ETL.
- `Delete Logs`: removes historical log data for this ETL.
- `Heartbeat`: appears only when the ETL may still be marked as running; use it to clear a stuck running state.
- `Save As`: saves the ETL as a new report definition.
- `Save`: saves changes to the current ETL.

### Live Refresh

Status-style tabs show a small green live dot. While the dot is active, the tab refreshes automatically. Click the dot to pause or resume live refresh.

## Create or Update an ETL

### 1. Connections

Use the Connections tab to choose:

- `Source Connection`: the system or file source to read from.
- `Destination Connection`: the SQL Server database that will receive the data.
- `SSAS Connection`: only needed when SSAS processing is enabled.

Notes:

- The destination must be a SQL Server connection.
- The `...` button next to a connection opens the connection record for editing.

### 2. Available

The Available tab displays the source objects that RpETL discovered from the selected source.

Columns vary by source type, but commonly include:

- table name,
- row count,
- column count,
- whether created and modified date columns exist,
- primary key,
- object type such as table or view.

Actions on this tab:

- `Refresh`: reloads source metadata.
- `Excel`: exports the available-object list.
- `Select All`: checks every currently displayed item.
- `Unselect All`: clears every current selection.
- Search box: filters the grid.

Important behavior:

- The page caches the available-table list in the browser for faster redisplay. Use `Refresh` when source metadata has changed.
- If `Select all tables` is enabled under Options, new source tables discovered in the future are also eligible to load.
- `Show views` in Options controls whether views are included in the Available list.

### 3. Selected

The Selected tab shows the tables and custom queries that belong to the ETL.

For each selected item, you can review:

- table or query name,
- modified date setting,
- primary key,
- whether history tracking is enabled.

Click the edit icon for a selected table to open table-specific settings.

#### Table Settings Dialog

For a selected physical table, you can configure:

- `PK`: override the detected primary key.
- `Check for duplicates`: validates whether the chosen PK is unique.
- `Check for nulls`: validates whether the chosen PK contains nulls.
- `Modified date`: keep the default, choose a source date column, or select `Do full reload`.
- `History tracking (hist_)`: enables SCD2 history for that table when history is not already forced globally.
- `Ignore duplicates`: available when duplicate keys are detected and you want the load to continue.
- `Excel Sheet`: available for `.xlsx` sources so you can choose the worksheet.
- `Post Process SQL`: available when the ETL-level `Post Process SQL` option is turned on.

You can also remove the table from the ETL by clicking `Delete` in the dialog.

#### XML Sources

If the selected source object is an XML file, the table dialog also supports XSL management:

- upload an XSL file,
- download the current XSL file,
- test the XSL by rendering transformed output,
- delete the XSL.

XML loads depend on an XSL transform so the XML can be shaped into rows and columns.

### 4. Queries

The Queries tab manages custom SQL extracts. A custom query behaves like a selected table but uses SQL you provide instead of a physical table.

Use `Add Custom Query` to create one.

Each custom query includes:

- `Query alias`: the name used inside RpETL.
- `PK`: optional primary key for merge and incremental behavior.
- `Modified date`: optional date column for incremental loading.
- `SQL`: the query text.
- `Load history (hist_)`: enables history tracking for the query result.
- `Post Process SQL`: available when ETL-level post-process SQL is enabled.

Buttons in the custom query dialog:

- `Test`: runs the SQL and previews the result set.
- `Get Columns`: reads the query metadata and fills the PK and Modified date drop-downs.
- `Save Query`: stores the custom query inside the ETL definition.
- `Delete`: removes the custom query.

Recommendations:

- Use `Get Columns` after changing the SQL.
- Set a PK whenever possible.
- Set a Modified date only when the query exposes a stable date column suitable for incremental logic.

## Options Tab

The Options tab controls global ETL behavior.

### Default Date Columns

- `Created date column`: default created-date column name across source tables.
- `Modified date column`: default modified-date column name across source tables.

These defaults help RpETL decide whether a table can be loaded incrementally.

### Performance and Delete Handling

- `Number of tables being processed at the same time`: parallelism for the ETL run.
- `Process deletes`: controls how often delete detection is allowed for incremental tables.

Delete options include:

- every run,
- no more than once per hour,
- no more than once per day,
- no more than once per week,
- no more than once per month,
- do not delete.

### Prefixes

These prefixes determine destination object naming:

- `Staging table prefix`: default `stg_`
- `Staging table primary keys prefix`: default `stg_id_`
- `Reporting table prefix`: often blank
- `Historical tracking (SCD2) table prefix`: default `hist_`

### Global Checkboxes

- `Historical tracking (SCD2) for all tables`: enables history tracking everywhere.
- `Use row hash for SCD2 when source table has N or more columns`: improves history comparison for wider tables.
- `User can run manually by clicking Run button`: allows manual execution from the UI.
- `Load data once`: intended for static file loads; disables history, full reload, and change tracking options.
- `Reload already loaded today`: allows another load even if the table already ran today.
- `Full reload`: forces full reload behavior for the ETL.
- `Post Process SQL`: reveals SQL areas used to run custom SQL after data loads.
- `Use SQL Server Change Tracking to load data incrementally`: available for SQL Server sources when supported.
- `Select all tables`: treats all discovered tables as selected.
- `Show views`: includes views in discovery.
- `Table must have created date`: filters the Available list.
- `Table must have PK`: filters the Available list.
- `SSAS processing`: enables SSAS post-load processing.
- `Copy schema name`: preserves source schema names when supported.
- `Ignore blob columns`: skips blob columns to avoid memory-heavy loads.

### Change Tracking

If the source is SQL Server, the Options tab may show controls for Change Tracking.

Possible states:

- not available for non-SQL Server sources,
- database-level change tracking can be enabled,
- table-level change tracking can be enabled after the database is ready.

Use this feature only when the source SQL Server database and tables are configured for Change Tracking and your account has the required permissions.

## SQL Tab

The SQL tab is visible when `Post Process SQL` is enabled.

Use it to store ETL-level SQL that should run after table processing completes. Similar post-process SQL fields are also available for individual tables and custom queries when this option is on.

Typical uses:

- refresh helper tables,
- apply indexes or cleanup steps,
- run post-load business logic.

## Scheduler Tab

The Scheduler tab defines when the ETL should run automatically.

Available schedule types:

- `None`: no automatic schedule.
- `Hourly`: run every N hours and N minutes.
- `Daily`: repeat every N days.
- `Weekly`: run on selected weekdays.
- `Weekly skip`: run on selected weekdays every N weeks.
- `Week number`: run on a specific week-of-month and weekday within selected months.
- `Calendar`: run on selected calendar days within selected months.
- `Once`: run one time.

Schedule fields:

- `Start Time`: earliest allowed run time.
- `End Time`: latest allowed run time.
- `Effective Date`: first date when the schedule becomes active.
- `Expiration Date`: last active date.

Use the checkboxes to select weekdays, months, or day numbers depending on the chosen schedule type.

## Notifications Tab

The Notifications tab controls who gets notified for:

- `Error`
- `Success`

For each outcome you can select:

- users,
- roles.

Use this tab when support or business users need automatic notification of failures or successful completion.

## SSAS Tab

The SSAS tab is used only when `SSAS processing` is enabled in Options.

Use it to:

- select an SSAS connection,
- choose a database or objects to process,
- review or edit the XMLA command,
- run SSAS processing.

This is typically used at the end of an ETL cycle when downstream Analysis Services objects must be refreshed.

## Run and Monitor the ETL

### Manual Run

To run an ETL manually:

1. Save the ETL.
2. Confirm that `User can run manually by clicking Run button` is enabled if your environment restricts manual execution.
3. Click `Run`.
4. Open the Status tab to monitor progress.

While running, the page shows either:

- a progress bar based on recent run duration, or
- a spinner when no duration estimate is available.

### Status

The Status tab shows the current table-level progress of the ETL. Common values include:

- last loaded time,
- whether the table loaded today,
- load duration,
- source and destination counts,
- count difference,
- source and destination max dates,
- date difference.

Use this tab during and after a run to confirm which tables loaded and which ones still need attention.

### Logs

The Logs tab shows recent informational messages and processing milestones, for example:

- ETL start and end,
- extract and bulk-insert messages,
- heartbeat start and stop events,
- summary completion messages.

Use `Delete Logs` from the toolbar if you need to clear historical log entries for the ETL.

### Errors

The Errors tab shows the latest critical failures. Use it first when a run fails.

Typical examples:

- connection failures,
- authentication errors,
- SQL or source query errors,
- load procedure failures.

### Jobs

The Jobs tab shows executed job batches and helps answer:

- when a run started,
- how long it took,
- whether it is still running,
- how many table processes were launched,
- how many log messages were recorded.

### Stats

The Stats tab shows average performance from recent runs, including processing time for steps such as:

- process,
- extract full,
- extract delta,
- extract ID,
- load,
- load history.

Use this tab to identify slow tables and compare recent behavior.

### Counts

The Counts tab compares source and destination metrics to show whether data appears current.

Common columns include:

- source count,
- destination count,
- count difference,
- last source date,
- last destination date,
- date difference.

Use this tab to quickly spot tables that may need a refresh or investigation.

## Recommended Setup Order for a New ETL

1. Select source and destination connections.
2. Open Options and set global defaults such as date columns, prefixes, and discovery filters.
3. Open Available and refresh metadata.
4. Select tables, then review Selected for table-level overrides.
5. Add any custom queries.
6. Configure Notifications.
7. Configure Scheduler if the ETL should run automatically.
8. Save the ETL.
9. Run it once manually and review Status, Logs, Errors, and Counts.

## Troubleshooting Tips

- If Available does not show expected objects, verify the source connection and click `Refresh`.
- If table metadata looks stale, refresh the Available cache.
- If per-table editing warns `Save ETL first`, save the ETL before changing table-specific settings.
- If a selected PK may be invalid, use `Check for duplicates` and `Check for nulls`.
- If an XML source does not expose columns correctly, upload or correct the XSL file.
- If manual run is not available, enable `User can run manually by clicking Run button` and save.
- If Change Tracking does not appear, confirm the source is SQL Server.
- If Change Tracking appears but cannot be used, the database or source tables may still need to be enabled.
- If counts differ after a run, inspect Status, Logs, Errors, and Counts together before rerunning.

## Operational Notes

- Save often. Unsaved changes are not used by the scheduler.
- Use full reload only when incremental logic is not appropriate or when resynchronizing data.
- Use `Load data once` for static file-based loads that should not be reprocessed repeatedly.
- Keep PK and Modified date settings as simple and stable as possible.
- Turn on history tracking only when you need row-version history in destination tables.

## Summary

Most work in RpETL follows the same pattern: choose connections, select data sources, fine-tune options, save, run, and monitor the result. The page is designed so configuration happens in the first tabs and operational monitoring happens in the last tabs.
