Learn more about Microsoft SQL Server Import Integration.

You can write job results directly to your SQL Server tables.

For sample workflows on exporting to your SQL Server tables, view Treasure Boxes.


Prerequisites

  • Basic knowledge of Treasure Data.

  • A SQL Server instance.

Use the TD Console to Export Results

  1. Open the TD Console.

  2. Navigate to Data Workbench > Queries.

  3. Create a new query or select an existing query.

  4. Select Export Results.

  5. The Choose Integration dialog opens. Select one of two options:

    1. Use Existing Integration. Select the existing integration.


    2. Create New Integration. Select the integration type and edit the integration Name, Host, User, and Password.


      • If you are using Azure, omit the instance name and provide the port # only.

      • If you are not using Azure and want to use your own instance: make sure that you can connect to the database using only the instance name, without the port. The reason for this is that when port is set, the instance name will be ignored. The `JDBC` library does not try to resolve from name → port but instead uses the port in configuration. Check with a database connector tool without using the given port.

  6. Save the query with a name and run, or just run the query. After successful completion of the query, the results are exported to the specified SQL Server destination.

Use from CLI

The following command enables you to set a scheduled query with Result Output to SQL Server.

Designate your json_key and escape newline with backslash.

For example:

$ td sched:create scheduled_sqlserver "10 6 * * *" \
-d dataconnector_db "SELECT id,account,purchase,comment,time FROM payment_history" \
-r '{
  "type":"sqlserver",
  "user":"user",
  "database":"mydb",
  "table":"payments",
  "batch_size":16777216,
  "mode":"insert"
}'

FAQ for Microsoft SQL Server Result Output

MS SQL Server Result Output fails with “Connection reset” failure while exporting to Azure SQL Database

Azure SQL Database is a multitenant service. Timeout sometimes occurs due to resource problems. To avoid this problem, we recommend you to try the following workarounds:

  • First dump to Azure Blob Storage with [Azure Blob Storage Data Result Output](/articles/result-into-microsoft-azure-blob-storage), then export to Azure SQL Database.

  • Decrease `batch_size` option's value like 3000 to reduce byte size of every insert request. Note that this change may cause long job running time.


  • No labels