InMemory SYS Tables

Ole Dyring

January 23, 2025 09:22

[Cloud release date: 2024.08.18]
[On-prem release: 2025 January]
[On-prem build: 25.01.21002]

Data stored in SYS tables can help you track reasons for ETL scripts failing, can help you optimize your script execution, can help you getting more details about your data.

Selecting from the SYS tables allows you to access certain details of your data (tables, columns), errors (exceptions) or web requests (requests, responses, etc.). Additionally, certain drivers can implement special commands (variables, tables.offline, sheets) depending on their needs.

This information is stored on Connection-level, meaning information there is accumulated by all the imports/consumers of that connection until it is released/closed.

Supported SYS tables by Driver

Below is a list of all supported SYS tables by all current (June 2024) drivers and online data providers.

Driver/Online Provider	sys.exceptions	sys.columns	sys.tables	sys.tables.offline	sys.sheets	sys.variables	sys.requests	sys.requests.headers	sys.responses	sys.responses.headers
AX7	yes	yes	yes	yes	no	no	no	no	no	no
CSV	yes	no	no	no	no	no	yes	yes	yes	yes
Excel	yes	no	no	no	yes	no	yes	yes	yes	yes
Odata	yes	no	no	no	no	no	no	no	no	no
DB2	yes	no	no	no	no	no	no	no	no	no
DenmarkStatistics	yes	no	no	no	no	no	no	no	no	no
Email	yes	no	no	no	no	no	no	no	no	no
EnvVariable	yes	no	no	no	no	yes	no	no	no	no
FileList	yes	no	no	no	no	no	no	no	no	no
FixedWidth	yes	no	no	no	no	no	no	no	no	no
Generic Database	yes	no	no	no	no	no	no	no	no	no
GoogleAnalytics	yes	no	no	no	no	no	no	no	no	no
Javascript	yes	no	no	no	no	no	yes	yes	yes	yes
JSON	yes	yes	yes	no	no	no	yes	yes	yes	yes
Python	yes	no	no	no	no	no	no	no	no	no
R	yes	no	no	no	no	no	no	no	no	no
Salesforce	yes	no	no	no	no	no	no	no	no	no
STFP	yes	no	no	no	no	no	no	no	no	no
TARGIT Analysis	yes	no	no	no	no	no	no	no	no	no
Weather service	yes	yes	yes	no	no	no	no	no	no	no
XML	yes	yes	yes	no	no	no	yes	yes	yes	yes

Driver/Online Provider	sys.exceptions	sys.columns	sys.tables	sys.tables.offline	sys.sheets	sys.variables	sys.requests	sys.requests.headers	sys.responses	sys.responses.headers
Dynamics BC	yes	yes	yes	no	no	no	yes	yes	yes	yes
Dynamics BC Designer	yes	no	no	no	no	no	yes	yes	yes	yes
Dynamics BC v.2.0	yes	yes	yes	no	no	no	yes	yes	yes	yes
Dynamics BC v.2.0 Designer	yes	no	no	no	no	no	yes	yes	yes	yes
Dynamics CRM Designer	yes	no	no	no	no	no	yes	yes	yes	yes
Dynamics CRM Odata	yes	no	no	no	no	no	yes	yes	yes	yes
Dynamics CRM SQL	yes	yes	yes	no	no	no	yes	yes	yes	yes
Google Analytics	yes	no	no	no	no	no	yes	yes	yes	yes
Generic REST API	yes	no	no	no	no	no	yes	yes	yes	yes
HubSpot	yes	no	no	no	no	no	yes	yes	yes	yes
Jira Generic GET	yes	no	no	no	no	no	yes	yes	yes	yes
Jira Generic POST	yes	no	no	no	no	no	yes	yes	yes	yes
Jira Get all users	yes	no	no	no	no	no	yes	yes	yes	yes
Jira Get user	yes	no	no	no	no	no	yes	yes	yes	yes
Jira Get all projects	yes	no	no	no	no	no	yes	yes	yes	yes
Jira Get project	yes	no	no	no	no	no	yes	yes	yes	yes
Jira Search	yes	no	no	no	no	no	yes	yes	yes	yes
Jira Server Find users	yes	no	no	no	no	no	yes	yes	yes	yes
Jira Server Get user	yes	no	no	no	no	no	yes	yes	yes	yes
Jobindsats	yes	no	no	no	no	no	yes	yes	yes	yes
Salesforce - Generic GET	yes	no	no	no	no	no	yes	yes	yes	yes
Salesforce	yes	no	no	no	no	no	yes	yes	yes	yes
SharePoint Designer	yes	no	no	no	no	no	yes	yes	yes	yes
SharePoint Odata	yes	no	no	no	no	no	yes	yes	yes	yes
SharePoint SQL	yes	yes	yes	no	no	no	yes	yes	yes	yes
Statistics Denmark	yes	no	no	no	no	no	yes	yes	yes	yes

sys.exceptions

Whenever a driver is capable of detecting and processing an exception during data load it will store information about that exception and any nested exceptions in a special sys.exceptions table.

Exception message, stack trace, type are available, as well as references to Query that threw that exception (if present, see QueryId paragraph) and links between nested exceptions.

sys.tables

This sys query is implemented in drivers that are capable of specifying a table in a select query, and a range of possible options is present (i.e. due to complex structure of an input file or amount of tables in underlying database).

sys.columns

This sys query is implemented in drivers that are capable of specifying a table in a select query, and a range of possible options is present (ie due to complex structure of an input file or amount of tables in underlying database).

Filtering by tablename is supported.

sys.requests

This table will gather information about outgoing requests sent to another URL.

Information about Uri, http methods, body, timeouts & timings is available, as well as QueryId reference (see QueryId paragraph)

sys.requests.headers

An additional table that stores headers values of corresponding requests if available.

QueryId is also available (see QueryId paragraph)

sys.responses

This table will gather information about incoming responses from the requested resourses.

QueryId is also available (see QueryId paragraph)

SaveSysResponses is also available (see SaveSysResponses paragraph) - by default this table will not save response details (to save RAM), unless an option is specified on command level.

sys.responses.headers

An additional table that stores headers values of corresponding responses if available.

QueryId is also available (see QueryId paragraph)

sys.sheets

A special command for Excel driver that will iterate any (unhidden) sheet in a document.

sys.tables.offline

A special command for AX 7 (Dynamics 265 FO) driver that will iterate through offline list of available tables. Note - currently is no longer maintained.

sys.variables

A special command of Environmental Variable driver, holds info for existing envvars on system

Additional options

It is possible to fine tune sys.X table requests by using one of the following options inside of an OPTIONS section of a query

QueryId

This parameter will help you correlate the query that is being executed to i.e. an exception or web request as part of sys.exceptions, sys.requests, sys.responses etc tables.

It's idea is to help in case multiple exceptions/requests can be stored in sys.X table and a parallel block of imports is being executed preventing you from properly reach to a thrown exception inside of a TRY-CATCH block

It is also possible to filter sys.exceptions, sys.requests, sys.responses etc. table by QueryId

SaveSysResponses

This parameter controls whether data from a web response is stored in a connection or not.

By default it is disabled, in order to save RAM and not duplicate data stored by the driver, see below data with and without this setting enabled

select * from [url] (OPTIONS: SaveSysResponses=false;)

select * from [url] (OPTIONS: SaveSysResponses=true;)

Examples

This example extract data from the sys.responses.headers table.

The data extracted is:

"x-rate-limit-remaining", which is a value returned by the source data system's API interface. The value tells you how many requests you have left within the current minute. If you exceed this limit the API request will fail.
"Date", which is a timestamp returned by the source data system's API interface. From this timestamp, the seconds value is extracted. If the x-rate-limit-remaining reaches 0, the seconds value is used to insert a pause until the next full minute (mm:00) is reached, where the x-rate-limit-remaining is reset to 200, and the requests may proceed.

InMemory SYS Tables

Supported SYS tables by Driver

sys.exceptions

sys.tables

sys.columns

sys.requests

sys.requests.headers

sys.responses

sys.responses.headers

sys.sheets

sys.tables.offline

sys.variables

Additional options

QueryId

SaveSysResponses

Examples

Comments

Articles in this section