# SyteLine Incremental Extract Query Generator for SSIS and ETL

> Source: https://netray.co/tools/syteline-incremental-extract-query-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Writes the watermark table, delta SELECT, watermark update, delete detection and MERGE for loading one SyteLine table into a staging database with SSIS or any ETL tool.

## Category

developer

## Erp

Infor SyteLine

## Audience

- Data engineer
- SyteLine developer
- DBA
- BI developer

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Source table

**Key:** table

**Type:** text

**Default:** coitem_mst

**Help:** One _mst table per run, for example item_mst, co_mst, coitem_mst, job_mst, matltran_mst.

#### Business columns to extract

**Key:** cols

**Type:** textarea

**Default:** co_num, co_line, co_release, item, qty_ordered, qty_shipped, stat, due_date

#### MERGE update list (t.col = s.col)

**Key:** set_clause

**Type:** textarea

**Default:** t.co_num = s.co_num, t.co_line = s.co_line, t.co_release = s.co_release, t.item = s.item, t.qty_ordered = s.qty_ordered, t.qty_shipped = s.qty_shipped, t.stat = s.stat, t.due_date = s.due_date, t.RecordDate = s.RecordDate

#### MERGE insert values (s.col list)

**Key:** insert_vals

**Type:** textarea

**Default:** s.co_num, s.co_line, s.co_release, s.item, s.qty_ordered, s.qty_shipped, s.stat, s.due_date

#### Site (site_ref)

**Key:** site

**Type:** text

**Default:** MAIN

#### Watermark column

**Key:** wm_col

**Type:** select

**Options:**

- RecordDate
- CreateDate

**Default:** RecordDate

**Help:** RecordDate catches inserts and updates; CreateDate only catches new rows.

#### Overlap minutes

**Key:** overlap

**Type:** number

**Default:** 5

#### Watermark control table

**Key:** wm_table

**Type:** text

**Default:** etl_watermark

#### Landing table in staging DB

**Key:** land

**Type:** text

**Default:** land_coitem

#### Target table in staging DB

**Key:** target

**Type:** text

**Default:** stg_coitem

**Blocks:**

#### 1. Watermark table (staging database, not the SyteLine database)

**Body:** CREATE TABLE dbo.{{wm_table}} (
    table_name sysname      NOT NULL,
    site_ref   nvarchar(20) NOT NULL,
    last_value datetime2(3) NOT NULL,
    CONSTRAINT PK_{{wm_table}} PRIMARY KEY (table_name, site_ref)
);

INSERT INTO dbo.{{wm_table}} (table_name, site_ref, last_value)
VALUES (N'{{table}}', N'{{site}}', '1900-01-01');

#### 2. Delta extract (run on the SyteLine reporting replica)

**Body:** -- @LastValue comes from the watermark table, @RunStart is captured once at the start of the run
DECLARE @Site nvarchar(20) = N'{{site}}';
DECLARE @Overlap int = {{overlap}};
DECLARE @LastValue datetime2(3) = ?;   -- parameter mapped by SSIS/ADF
DECLARE @RunStart datetime2(3) = ?;    -- parameter mapped by SSIS/ADF

SELECT
    {{cols}},
    RowPointer,
    RecordDate,
    CreateDate
FROM dbo.{{table}}
WHERE site_ref = @Site
  AND {{wm_col}} > DATEADD(minute, -@Overlap, @LastValue)
  AND {{wm_col}} <= @RunStart;

#### 3. Advance the watermark (only after the load commits)

**Body:** -- Separate batch: map the same @RunStart value captured for block 2 to the ? marker
UPDATE dbo.{{wm_table}}
SET last_value = ?
WHERE table_name = N'{{table}}'
  AND site_ref = N'{{site}}';

#### 4. Delete detection (land the full key list, then compare)

**Body:** -- Extract on the source replica into dbo.{{land}}_keys:
-- SELECT RowPointer FROM dbo.{{table}} WHERE site_ref = N'{{site}}';

-- Then in the staging database:
DELETE t
FROM dbo.{{target}} t
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.{{land}}_keys k
    WHERE k.RowPointer = t.RowPointer
);

#### 5. MERGE landed delta into the staging table on RowPointer

**Body:** MERGE dbo.{{target}} AS t
USING dbo.{{land}} AS s
    ON t.RowPointer = s.RowPointer
WHEN MATCHED AND s.RecordDate > t.RecordDate THEN
    UPDATE SET {{set_clause}}
WHEN NOT MATCHED BY TARGET THEN
    INSERT ({{cols}}, RowPointer, RecordDate)
    VALUES ({{insert_vals}}, s.RowPointer, s.RecordDate);

TRUNCATE TABLE dbo.{{land}};

**Notes:** Verify on your SyteLine 9/10 database: that RowPointer is uniqueidentifier and unique on the table, and that RecordDate is refreshed on every update for the tables you extract (check the update triggers or update a test row and compare). The ? markers are OLE DB parameter placeholders for an SSIS Execute SQL or source task; replace them with @-parameters for ADO.NET or ADF. Keep the column lists in the three fields in the same order. Extract from a reporting replica, never through views that rely on the session site context.

## Content

**Intro:** This generator writes a complete watermark pattern for pulling one Infor SyteLine 9/10 table into a staging database incrementally with SQL Server Integration Services, Azure Data Factory or any ETL tool. It uses the system columns every SyteLine _mst table carries, RowPointer as the stable key and RecordDate as the change marker.

**Sections:**

#### How to use it

**Body:** Enter the source table and the business columns you need, then the matching MERGE update and insert lists in the same order. Block 1 runs once in your staging database to create the control table and seed it with an old date. Each scheduled run then does four steps: read last_value from the control table and capture the run start time, execute block 2 on the SyteLine replica and land the rows into the landing table, run block 5 to MERGE them into the staging table, and finally run block 3 to move the watermark to the captured run start. Run block 4 on a slower schedule, nightly or weekly, for tables where deleted rows matter. In SSIS this maps to an Execute SQL task, a data flow and two more Execute SQL tasks inside a sequence container.

**Bullets:**

- Seed with 1900-01-01 so the first run is a full load
- Capture @RunStart once, before the extract, and reuse it for the update
- Update the watermark only after the MERGE commits

#### Why RowPointer and RecordDate

**Body:** SyteLine tables carry a set of system columns: RowPointer, RecordDate, CreateDate, CreatedBy, UpdatedBy and NoteExistsFlag. RowPointer is a GUID that identifies the row for the lifetime of the record, so it is a better merge key than business keys that users can renumber or that span several columns. RecordDate is maintained by SyteLine on change, which makes it a usable high-water mark for inserts and updates. CreateDate only moves on insert, so use it as the watermark only for append-only tables. The overlap window re-reads a few minutes before the last watermark, which covers transactions that committed late with an earlier timestamp. The MERGE condition on RecordDate stops a re-read row from overwriting a newer copy.

**Bullets:**

#### Worked example

**Body:** For coitem_mst at site MAIN with a 5 minute overlap: the last run finished with a watermark of 06:00:00. The 07:00 run captures @RunStart 07:00:00 and extracts rows with RecordDate after 05:55:00 and up to 07:00:00. Suppose 1,240 order lines changed; perhaps 40 of them were already loaded last hour and come back because of the overlap. The MERGE updates those 40 only if their RecordDate moved, inserts genuinely new RowPointers, and the watermark becomes 07:00:00. If the load fails part-way, the watermark stays at 06:00:00 and the next run simply repeats the window, because the pattern is idempotent.

**Bullets:**

#### Gotchas

**Body:** Deletes never show up in a RecordDate delta, which is why block 4 compares full RowPointer sets. Mass updates through SQL scripts that disable triggers will not move RecordDate, so after any data fix run a full reload of that table. Clock skew between the replica and the ETL server matters less than it seems because the window uses the SyteLine server time stored in the rows, but always take @RunStart from the source server. Read from an Always On readable secondary or a log-shipped copy, not production. Avoid SyteLine views that filter by the session site: an ETL login has no site context and can return nothing or the wrong site. Index RecordDate on large tables such as matltran_mst only after checking the impact with your DBA.

**Bullets:**

**Cta:** If you want this staging data to feed a private assistant that answers SyteLine questions on your own servers, we can build that on top of the same extract.

**Faq:**

#### What is SQL Server Integration Services used for with SyteLine?

**Answer:** SSIS is Microsoft's ETL tool that ships with SQL Server. With SyteLine it typically moves data from the ERP database into a reporting or warehouse database on a schedule, so heavy reporting, Power BI models and AI retrieval run against a copy instead of the live transactional database.

#### How do I do an incremental load in SSIS?

**Answer:** Keep a watermark table with the last loaded timestamp per source, read it into a variable, extract only rows changed after it (minus a small overlap), load into a landing table, MERGE into the target on a stable key, then advance the watermark. This generator writes each of those statements for a SyteLine table.

#### Is RowPointer unique in SyteLine tables?

**Answer:** RowPointer is the uniqueidentifier system column that SyteLine and the IDO layer use to identify a row, and it is unique per table. Confirm the data type and that a unique index exists on your version with sp_help before you rely on it as the merge key.

#### Why not use SQL Server change tracking instead?

**Answer:** Change tracking or CDC can be better for very high volume tables, but they need database-level configuration on the ERP database and DBA ownership through upgrades. The RecordDate watermark needs no change to the SyteLine database and works from a read-only replica, which is why it is the usual first step.

## Search terms

sql server integration services, microsoft sql server integration services, sql server integration tools, sql server integration services tutorial, integration services in sql server, syteline incremental load, syteline rowpointer recorddate, syteline data warehouse extract

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json