Native Data Type (Term Mapping)

Native Data Type (NativeDataType) casts the increment threshold of an incremental Stage load to a data type of the source system. Use it when the source compares the increment term in its own type, for example an Oracle TIMESTAMP.

The property exists only in the Microsoft SQL Server - Stage OpenQuery generator.

Where to set it

Set the property when you edit the term mapping of BG_IncrementalFilter in a Stage. It is a text field and empty by default. It has an effect only on this mapping and only with an incremental load.

Values

• Empty (default): the native query compares the increment term with the threshold as text.
• A data type of the source system, for example DATETIMEOFFSET or TIMESTAMP: the native query compares the increment term with CAST('<threshold>' AS <native data type>).

Write the type in the syntax of the source system, because the linked server passes it unchanged. The threshold is converted to text first: dates as yyyymmdd, timestamps as yyyy-mm-dd hh:mi:ss with fractions.

Example

A Stage load_with_native_datatype maps BG_IncrementalFilter to TRANSACTION_TIME, with Native Data Type DATETIMEOFFSET. The native query in its loader ends with:

WHERE ' + CASE WHEN @IncrementPrevious IS NOT NULL
    THEN N'[TRANSACTION_TIME] > CAST(''''' + CONVERT(NVARCHAR(MAX),
        @IncrementPrevious, 121) + ''''' AS DATETIMEOFFSET)'
    ELSE N'1 = 1' END + N'

On the first load, @IncrementPrevious is NULL, and the query reads all rows.

Related articles

• Reference: BG_IncrementalFilter, Native Expression (Term)
• See also: Configure an incremental load, Model a Stage OpenQuery Solution