Contents x
Microsoft SQL Server Output
  • Dark
    Light
Contents

Microsoft SQL Server Output

  • Dark
    Light
This article is specific to the following platforms - Snowflake - Redshift.

Microsoft SQL Server Output Component

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.

Snowflake Properties

Property Setting Description
Name String A human-readable name for the component.
Endpoint String The Microsoft SQL Server endpoint.
Database Name String Your Microsoft SQL Server database name.
Username String Your Microsoft SQL Server username.
Password String The password corresponding to your Microsoft SQL Server username.
JDBC Options Parameter A JDBC connection parameter. For more information, read Connection options.
Value The value of the corresponding JDBC connection parameter.
Database Select Select the Snowflake database. The special value, [Environment Default], will use the database defined in the Matillion ETL environment.
Schema Select Select the schema for the source table. The special value, [Environment Default], will use the schema defined in the Matillion ETL environment.
Source Table Select 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 Column Select Use the arrow buttons to select which columns to load. Move columns to the right to include them in the load. By default, all columns will be selected for the load.
Table Maintenance Select Define how the target table is treated.
Create If Not Exists: if the named target table does not yet exist, it will be created.
None: assume the MSSQL Server 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 Column Select A column or a group of columns used to identify a row uniquely in a table.
Update Strategy Select 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 Select 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 Select Specify whether an output load should Continue or Fail if an ANSI Warning message is generated.
Additional Copy Options Select 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.

Redshift Properties

Property Setting Description
Name String A human-readable name for the component.
Endpoint String The Microsoft SQL Server endpoint.
Database Name String Your Microsoft SQL Server database name.
Username String Your Microsoft SQL Server username.
Password String The password corresponding to your Microsoft SQL Server username.
JDBC Options Parameter A JDBC connection parameter. For more information, read Connection options.
Value The value of the corresponding JDBC connection parameter.
Source Schema Select Select the schema for the source table. The special value, [Environment Default], will use the schema defined in the Matillion ETL environment.
Source Table Select 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 Column Select Use the arrow buttons to select which columns to load. Move columns to the right to include them in the load. By default, all columns will be selected for the load.
Table Maintenance Select Define how the target table is treated.
Create If Not Exists: if the named target table does not yet exist, it will be created.
None: assume the MSSQL Server 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 Column Select A column or a group of columns used to identify a row uniquely in a table.
Update Strategy Select 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 Select 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 Select Specify whether an output load should Continue or Fail if an ANSI Warning message is generated.
Additional Copy Options Select 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.

Video

What's Next

Cookie consent

We use our own and third-party cookies to understand how you interact with our knowledge base. This helps us show you more relevant content based on your browsing and navigation history.