Skip to main content

Local SQL Server Data Source

Note: This data source supports direct connectivity to a local or on-premises SQL Server instance. Ensure the SQL Server is accessible from the machine running the Jet Analytics Execution Service before proceeding.

This article describes the Local SQL Server data source available for Ingest instances. This data source connects directly to an on-premises or locally accessible SQL Server database, without requiring cloud storage intermediaries.

Set up a Local SQL Server

Step 1: Verify SQL Server accessibility

Before configuring the data source, confirm that:

  • The SQL Server instance is reachable from the machine where the Jet Analytics Execution Service is running.
  • The appropriate SQL Server port (default: 1433) is open and not blocked by a firewall.
  • The target database containing F&O data exists and is online.

Step 2: Provision a SQL Server login

Create or identify a SQL Server login with the following minimum permissions on the F&O database:

  • SELECT on all relevant tables and views.
  • VIEW DATABASE STATE (if metadata caching is enabled).
  • Read access to system catalog views (for example, sys.tables, sys.columns) for schema discovery.

Step 3: Add the data source in Jet Analytics Data Integration

  1. In Jet Analytics Data Integration, open the Ingest instance.
  2. Right-click Data Sources and select Add Data Source.
  3. Select Local SQL Server from the list of available data source types.
  4. Enter a descriptive name for the data source.

Step 4: Configure connection settings

Enter the required connection parameters (see Configuration settings — Connection settings below). Test the connection to verify credentials and network reachability before saving.

Step 5: Configure company and options behavior

If the F&O database contains multi-company data, configure the Companies section to include or exclude specific legal entities. If option labels are required for reporting, enable and configure the Options behavior section.

Step 6: Add tables and deploy

  1. Right-click the new data source and select Add All Tables, or selectively add the tables required.
  2. Map fields and apply any transformations as needed.
  3. Execute the Ingest instance to transfer data into your configured storage.

Configuration settings

1. Connection settings

  • Server: The hostname or IP address of the SQL Server instance. For named instances, use the format ServerName\InstanceName.
  • Port: The TCP port the SQL Server is listening on. Defaults to 1433 if left blank.
  • Database: The name of the database containing the F&O data.
  • Authentication type: The authentication method — either SQL Server Authentication (username and password) or Windows Authentication (uses the credentials of the Execution Service account).
  • Username: The SQL Server login name. Required when using SQL Server Authentication.
  • Password: The password associated with the SQL Server login. Required when using SQL Server Authentication.
  • Connection timeout: Time in seconds before the connection attempt times out if there is no server response.
  • Command timeout: The maximum time in seconds allowed for a command to execute before it times out.
  • Encrypt connection: When enabled, all data transmitted between Jet Analytics Data Integration and SQL Server is encrypted using TLS. Recommended for production environments.
  • Trust server certificate: When enabled, the server's TLS certificate is accepted without validation. Use only in trusted internal environments.

2. Companies

  • Companies: A list of company IDs (DataAreaId values) to include or exclude, depending on the Include All Companies setting.
  • Include All Companies:
    • Enabled: All companies are included except those listed in the Companies field.
    • Disabled: Only the companies listed in the Companies field are included.
  • Compare Companies Case-Insensitive: When enabled, company IDs are converted to lowercase before comparison, ensuring consistent matching regardless of letter case.
  • Company Column Name: The name of the column used to identify the company in each table. Typically DATAAREAID in standard F&O SQL schemas.

3. Options behavior

  • Enable Options: Enables the extraction of option metadata from the data source.
  • Option Table Name: The name of the table or view containing option set metadata, typically a custom or replicated options table in the local SQL database.
  • Apply Option Labels on Tables: When enabled, a new column is added alongside each option column, postfixed with _Label, containing the human-readable label for the option value.
  • Option Localized Label Language Code: The language code for retrieving localised option labels. Defaults to 1033 (English) if left blank.
  • Create Localized Option Table: When enabled, a consolidated table containing all options and their labels is created. An entity name column is added to each table to simplify joins.

4. Metadata settings

  • Cache Metadata: When enabled, schema metadata (table and column definitions) is cached locally to improve performance on subsequent connections.
  • Metadata Cache Tolerance: The duration in minutes for which the cached metadata remains valid before a refresh is triggered.
  • Metadata Cache Location: The local folder path where metadata cache files are stored. Ensure the Execution Service account has read and write access to this directory.

5. Execution settings

  • Fetch Size: The number of rows retrieved per batch during data extraction. Increasing this value can improve throughput on high-latency connections; reducing it can lower memory consumption.
  • Max. Parallelism: The maximum number of concurrent threads used when extracting data from multiple tables simultaneously. Adjust based on available SQL Server resources and network capacity.
  • Local Work Location: The local directory used to temporarily store intermediate data files during execution. Ensure sufficient disk space is available for the expected data volumes.

6. Debugging

  • Enable Debugging: When enabled, extended diagnostic information — including generated SQL queries, execution timings, and intermediate result sets — is retained in the local work location for review.
  • Log Level: Controls the verbosity of debug output. Options include Error, Warning, Info, and Verbose. Set to Verbose when diagnosing connection or extraction issues.

Was this article helpful?

We're sorry to hear that.