Microsoft SQL Server Output

Overview

The Microsoft SQL Server Output component enables users to output the contents of a table (or view) from their cloud data warehouse (CDW) to their Microsoft SQL Server database.

Using this component, Matillion ETL users can push their data to an on-prem server from the cloud if they so wish.

Properties

SnowflakeAmazon Redshift

Name = string

A human-readable name for the component.

Endpoint = string

The Microsoft SQL Server endpoint. For example, <hostname>:<port>.

Database Name = string

Your Microsoft SQL Server database name.

Username = string

Your Microsoft SQL Server username.

Password = string

The corresponding password. Store the password in the component, or create a managed entry for the password using Manage Passwords (recommended).

JDBC Options = column editor

Parameter: A JDBC connection parameter. For more information, read Connection options.
Value: A value for the given Parameter.

Database = drop-down

The Snowflake database. The special value, [Environment Default], will use the database defined in the environment. Read Databases, Tables and Views - Overview to learn more.

Schema = drop-down

The Snowflake schema. The special value, [Environment Default], will use the schema defined in the environment. Read Database, Schema, and Share DDL to learn more.

Source Table = drop-down

The table in your cloud data warehouse that you wish to output to Microsoft SQL Server.

Target Table = string

Provide a name for the output table that is to be created.

Target Schema = string

The schema for the target table.

Load Columns = dual listbox

The columns from the source table to include in the output job. Use the arrow buttons to include or exclude columns. Columns on the right-hand side will be included. Columns on the left-hand side will be excluded.

Table Maintenance drop-down

Define how the target table is treated.

Create If Not Exists: If the named target table doesn't yet exist, it will be created.
None: Assume the SQL Server database already has the table defined with the correct structure.
Replace: if the named target table already exists, it will be dropped and replaced by a newly created table. Please use this setting with care.

Primary Key = dual listbox

Select one or more columns to be designated as the table's primary key.

Update Strategy = drop-down

In addition to inserting new records based on the primary key(s), this property can instruct Matillion ETL to:

Ignore: Existing rows with the same primary key values will be ignored.
Replace: Existing rows with the same primary key values will be replaced.

The default setting is Ignore. This property is only available after a primary key has been selected.

Truncate Target Table = drop-down

Yes removes all rows from a table or specified partitions of a table, without logging the individual row deletions. For more information, read TRUNCATE TABLE.
No: does not truncate the target table.

On Warnings = drop-down

Specify whether an output load should Continue or Fail if an ANSI Warning message is generated.

Additional Copy Options = multiple drop-downs

Additional copy options. Each option must be turned On for the setting to take effect.

Table lock: Obtain a bulk update lock for the duration of the bulk copy operation. The default setting is On.
Keep identity: Preserve source identity values. The default setting is On.
Keep nulls: Preserve null values in the destination table regardless of the settings for default values. The default setting is Off.
Check constraints: Check constraints while data is being inserted. The default setting is Off.
Fire triggers: Cause the server to fire the insert triggers for the rows being inserted into the database. The default setting is Off.

For more information, read SQL Server Bulk Copy Options.

Batch Size = integer

The number of rows to load to the target between each COMMIT. The default value is 5000.