SQL Database Destination reference

This reference explains in detail how to configure data transfer to SQL Database.

Prerequisites

Before you complete the procedure in this guide, perform all of the following actions:

Configuring data transfer to SQL Database

To configure data transfer to SQL Database, follow these steps:

  1. Click the Transfer element and select the Workspace you work with in Connect, Enrich & Transfer.

  1. Click the SQL Database Destination in the list.

  2. In the left navigation panel, click Configuration.

  3. Fill in the following fields:

    Load state table

    Select the checkbox to create a load state table. The load state table is a table in the Destination which contains information about each data transfer. The load state table contains the following fields:

    Fieldname

    Description

    datastream_id

    Datastream ID.

    datastream_name

    Datastream name.

    datastream_url

    Datastream URL.

    extract_filename

    Filename of the Data Extract.

    range_start

    Start date and time of the fetch.

    range_end

    End date and time of the fetch.

    uuid

    Unique ID of the data transfer to the target table.

    load_state

    State of the data transfer. The possible states are failed, finished, and running.

    load_start

    Date and time when the data transfer started.

    load_end

    Date and time when the data transfer finished.

    target_schema

    Data schema used in the target table.

    target_table

    Name of the target table.

    nrows

    Number of rows in the target table.

    ncols

    Number of columns in the target table.

    Name of load state table

    Enter a name for the load state table. The default name of a load state table is dt_loadstate.

    Options

    Specify the options for the database in JSON format.

    Default schema

    Specify the default schema used in the database.

  4. Click Save.

Technical details of transferring data to SQL Database

When transferring a Data Extract to SQL Database, Adverity creates the following additional columns in the target table:

dt_created

The date the Data Extract was created.

dt_updated

The date the Data Extract was last updated.

dt_filename

The automatically generated filename of the Data Extract.

In addition, Adverity creates a temporary table in SQL Database with the name _stage. This ensures that no partial updates are made to the database (guarantee of atomicity). Adverity deletes this temporary table after the data transfer is complete or has been aborted.

Troubleshooting: Data transfer to SQL Database fails after changing the table structure of the Data Extract

A data transfer to SQL Database may fail because of a change in the table structure of the Data Extract. To ensure the frictionless operation of production systems, Adverity only modifies existing tables in SQL Database if the changes are considered neutral, such as adding a new field to the table. Changes to the table structure of the Data Extract are not considered neutral. As a consequence, Adverity does not update the existing table in SQL Database resulting in failed data transfer.

The following actions change the table structure of the Data Extract:

  • You change the key columns in the Datastream's Schema Mapping. For more information on key columns, see Setting key columns.

  • You change the data type of a column in the Datastream's Schema Mapping. For more information on the data types used in Adverity, see Data types used in data harmonization.

  • You change the maximum character length of values in a column which contains String values.

To resolve this problem, follow these steps:

  1. In SQL Database, modify or drop the table that corresponds to the Datastream.

  2. In Adverity, transfer data from the Datastream to SQL Database.

Troubleshooting: Adverity cannot access SQL Database Destination

If Adverity cannot access the SQL Database Destination, the Destination may restrict access based on IP address.

To resolve this issue, follow these steps:

  1. Determine Adverity's IP address with a tool compatible with your operating system. To determine Adverity's IP address on a Windows machine, follow these steps:

    1. Click the Connect element and select the Workspace you work with in Connect, Enrich & Transfer.

    1. Copy the URL that you see in the browser. Remove https:// from the beginning of the URL and the part similar to /workspace-name/ from the end. For example, if the original URL is https://my-organization.datatap.adverity.com/my-workspace/, the result is my-organization.datatap.adverity.com.

    2. Start Command Prompt.

    3. Enter the following command:

      nslookup {my-organization.datatap.adverity.com}

      Replace {my-organization.datatap.adverity.com} with the result of the previous step.

    4. In the Non-authoritative answer section of the Command Prompt response, find the Address line. The value displayed is Adverity's IP address.

  2. Add Adverity's IP address to the whitelist of your Destination.