Loading data into Microsoft SQL Server

This guide explains how to load data into Microsoft SQL Server for further analysis.

Prerequisites

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

  • Create a datastream whose data you want to load into Microsoft SQL Server. For more information on creating a datastream, see Creating a datastream.

  • If this is the first time you are loading data into Microsoft SQL Server, you will need to create a login profile with the username and password that you will use when adding Microsoft SQL Server as a destination. You will also need to grant this login profile the required permissions. To do this, see the Advanced Microsoft SQL Server tips.

  • (Recommended) For faster data processing with the bulk insert function, create a database master key in Microsoft SQL Server. For more information, see the Microsoft documentation.

  • (Recommended) For faster data processing with the bulk insert function, set up Azure Blob storage for your workspace. For more information, see Setting up storage for data extracts.

Procedure

To load data from a datastream into Microsoft SQL Server, follow these steps:

  1. Add Microsoft SQL Server as a destination to the workspace which contains the datastream or to one of its parent workspaces.

  2. Assign the Microsoft SQL Server destination to the datastream.

    You can assign as many destinations to a datastream as you want.

    Some destinations require specific Data Mapping, such as Hubspot and Facebook Offline Conversions. If these Data Mapping requirements conflict, the destinations cannot be assigned to the same datastream.

  3. Configure load settings.

Adding Microsoft SQL Server as a destination

To add Microsoft SQL Server as a destination to a workspace, follow these steps:

  1. Go to the Destinations page.

  2. Click + Create destination.

  3. Search for and click Microsoft SQL Server.

  1. Choose how to authorize Adverity to access Microsoft SQL Server:

    • To use your details, click Access Microsoft SQL Server using your credentials.

    • To ask someone else to use their details, click Access Microsoft SQL Server using someone else's credentials.

      If you choose this option, the person you ask to create the authorization will need to go through the following steps.

  2. Click Next.

  1. In the authorization page, fill in the following fields:

    Hostname

    The server name of the destination database. For more information on the Microsoft SQL Server hostname, see the Microsoft documentation.

    Database

    Specify the name of the Microsoft SQL Server database into which you want to load the data.

    Username

    The username of the Microsoft SQL Server account.

    Password

    The password of the Microsoft SQL Server account.

  2. In the Configuration page, fill in the following fields:

    Name

    (Optional) Rename the destination.

    Azure storage

    (Recommended) Select the Azure Blob storage you set up for the workspace. Setting up Azure Blob storage for your workspace is necessary for faster data processing with the bulk insert function.

    Master key exists

    (Recommended) If Adverity detects a database master key in the Microsoft SQL Server destination, this field displays . A database master key is necessary for faster data processing with the bulk insert function.

    For more information on advanced configuration settings, see Advanced Microsoft SQL Server tips.

  1. Click Create.

Assigning Microsoft SQL Server as a destination

To assign the Microsoft SQL Server destination to a datastream, follow these steps:

  1. Go to the Datastreams page.

  2. Open the chosen datastream by clicking on its name.

  1. In the Load section, click + Add destination.

  1. Select the Microsoft SQL Server checkbox in the list.

  2. Click Save.

  3. For the automatically enabled destinations, in the pop-up window, click Yes, load data if you want to automatically load your previously collected data into the new destination. The following data extracts will be loaded:

    • All data extracts with the status collected if no other destinations are enabled for the datastream

    • All data extracts with the status loaded if the data extracts have already been sent to Adverity Data Storage or external destinations

    Alternatively, click Skip to continue configuring the destination settings or re-load the data extracts manually. For more information, see Re-loading a data extract.

Configuring settings for loading data into Microsoft SQL Server

To configure the settings for loading data into Microsoft SQL Server, follow these steps:

  1. Go to the Datastreams page.

  2. Open the chosen datastream by clicking on its name.

  1. In the Load section, find the Microsoft SQL Server destination in the list, and click Actions on the right.

  2. Click Destination settings.

  1. Fill in the following fields:

    Table name

    Specify the target table in the destination into which to load data from the datastream. The name can contain alphanumeric characters and underscores. For example, target_table. To specify a schema, use the syntax schemaName.tableName.

    By default, Adverity saves data from each datastream in a different table named {datastream_type}_{datastream_id} (for example, mailgun_83).

    You can specify the same target table for several datastreams. If a column is shared between datastreams, Adverity performs a full outer join and concatenates values. If a column is not shared between datastreams, Adverity writes null values in the relevant cells.

    • To create a new Microsoft SQL Server spreadsheet containing the data you load into Microsoft SQL Server, enter a name for the new spreadsheet into this field.

    You can use the following placeholders when creating new table names in the destination:

    Placeholder

    Description

    {datastream_id}

    The datastream ID.

    {datastream_type}

    The data source.

    {extract_id}

    The data extract ID.

    {meta[*]}

    Replace * with a metadata placeholder to use metadata in the table name. For example, {meta[datastream_URI]} uses the datastream URI as the table name. For more information on metadata and placeholders, see Using placeholders.

    {name}

    The automatically generated filename of the data extract.

    {scheduled_day}

    The day when the data fetch was scheduled to run.

    {scheduled_month}

    The month when the data fetch was scheduled to run.

    {scheduled_year}

    The year when the data fetch was scheduled to run.

    Truncate

    Select this checkbox to delete all rows from the relevant table in the destination before loading the latest data extract.

    If the Truncate checkbox is selected, the following overwrite conditions are not applied and all data will be deleted.

    If no overwrite options are selected, the loaded data is appended to the destination table.

    Datastream

    Select this checkbox to overwrite all data previously loaded into the destination from this datastream.

    Data loaded from other datastreams is not overwritten.

    Date Range

    Select a date column from your data extract to overwrite all data in the destination with a date that matches a date in this column.

    If the Datastream checkbox is selected, only the data loaded from the current datastream with matching dates will be overwritten.

    Filename

    Select this checkbox to overwrite the relevant data in the destination if a data extract with the same filename already exists in the destination.

    Key Columns

    Select this checkbox to overwrite data in the destination based on the key columns defined in the Data Mapping of the datastream. Adverity executes this overwrite option after all the other overwrite options. When you select this checkbox, the configuration to overwrite data based on dates does not have an effect.

  2. Click Save.

Advanced Microsoft SQL Server tips

Creating a login profile and granting permissions

When you load data into Microsoft SQL Server for the first time, you will need to create a login profile with the username and password that you will use to add Microsoft SQL Server as a destination. You will also need to grant this profile the required permissions. To do this, follow these steps:

  1. Choose a username and password for the login profile you want to create.

  2. Use the following SQL statement to create a login profile by replacing the placeholders {{USERNAME}} and {{PASSWORD}} with the username and password you want to use.

    USE master;
CREATE LOGIN {{USERNAME}} WITH password='{{PASSWORD}}';
-- create db user for the login profile
CREATE USER {{USERNAME}} FROM LOGIN {{USERNAME}} WITH DEFAULT_SCHEMA=dbo;
GO

  3. Use the following SQL statement to grant the required permissions to the login profile you have created. Replace the placeholders {{USERNAME}} and {{PASSWORD}} with the same username and password that you used in the previous step.

    USE <DB-NAME>;
CREATE USER {{USERNAME}} FROM LOGIN {{USERNAME}} WITH DEFAULT_SCHEMA=dbo;
-- grante create table and write access
GRANT CREATE TABLE TO {{USERNAME}};
GRANT SELECT, INSERT, DELETE, ALTER, EXECUTE, CONTROL ON SCHEMA::dbo TO {{USERNAME}};

As a result, you have created a login profile with the username {{USERNAME}} and the password {{PASSWORD}}, and you have granted the required permissions to this login profile. You can now use this username and password when adding Microsoft SQL Server as a destination.

Configuration

To configure the Microsoft SQL Server destination, follow these steps:

  1. Go to the Destinations page.

  2. Click the Microsoft SQL Server destination in the list.

  1. In the secondary menu, click Configuration.

  2. 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 load. 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.

  3. Click Save.

Technical details of loading data into Microsoft SQL Server

When loading a data extract into Microsoft SQL Server, 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 Microsoft SQL Server 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 load is complete or has been aborted.

Loading data in bulk into Microsoft SQL Server

You can load data in bulk into Microsoft SQL Server if you have one of the following storage methods configured in your workspace:

  • Azure Blob

Loading data in bulk into Microsoft SQL Server is currently supported only for Azure Microsoft SQL instances (*.database.windows.net) and Microsoft Synapse.

To load data in bulk into Microsoft SQL Server, you need a master key that allows Adverity to access the database and perform the bulk load. For more information on how to find the master key for your Microsoft SQL Server, see the Microsoft SQL Server documentation.

Enter the master key into Adverity when you set up or edit the storage authorization. For more information how to set up or edit your storage, see Setting up authorizations to store data extracts

Troubleshooting: Loading data into Microsoft SQL Server fails after changing the table structure of the data extract

Data may fail to load into Microsoft SQL Server 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 Microsoft SQL Server 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 Microsoft SQL Server resulting in failure when loading data.

The following actions change the table structure of the data extract:

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

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

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

To resolve this problem, follow these steps:

  1. In Microsoft SQL Server, modify or drop the table that corresponds to the datastream.

  2. In Adverity, load data from the datastream into Microsoft SQL Server.

Troubleshooting: Adverity cannot access Microsoft SQL Server destination

If Adverity cannot access the Microsoft SQL Server destination, the destination may restrict access based on IP address.

To resolve this issue, follow these steps:

  1. Find the IP address of your Adverity instance. To find the IP address of your Adverity instance, contact Adverity Customer Support.

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