Contents x
    Microsoft SQL Server Output
      • Dark
        Light
      Contents

      Microsoft SQL Server Output

        • Dark
          Light
        Article Summary

        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

        PropertySettingDescription
        NameStringA human-readable name for the component.
        EndpointStringThe Microsoft SQL Server endpoint.
        Database NameStringYour Microsoft SQL Server database name.
        UsernameStringYour Microsoft SQL Server username.
        PasswordStringThe password corresponding to your Microsoft SQL Server username.
        JDBC OptionsParameterA JDBC connection parameter. For more information, read Connection options.
        ValueThe value of the corresponding JDBC connection parameter.
        DatabaseSelectSelect the Snowflake database. The special value, [Environment Default], will use the database defined in the Matillion ETL environment.
        SchemaSelectSelect the schema for the source table. The special value, [Environment Default], will use the schema defined in the Matillion ETL environment.
        Source TableSelectThe table in your cloud data warehouse that you wish to output to Microsoft SQL Server.
        Target TableStringProvide a name for the output table that is to be created.
        Target SchemaStringThe schema for the target table.
        Load ColumnsColumn SelectUse 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 MaintenanceSelectDefine 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 KeyColumn SelectA column or a group of columns used to identify a row uniquely in a table.
        Update StrategySelectIn 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 TableSelectYes 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 WarningsSelectSpecify whether an output load should Continue or Fail if an ANSI Warning message is generated.
        Additional Copy OptionsSelectAdditional 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 SizeIntegerThe number of rows to load to the target between each COMMIT.

        Redshift Properties

        PropertySettingDescription
        NameStringA human-readable name for the component.
        EndpointStringThe Microsoft SQL Server endpoint.
        Database NameStringYour Microsoft SQL Server database name.
        UsernameStringYour Microsoft SQL Server username.
        PasswordStringThe password corresponding to your Microsoft SQL Server username.
        JDBC OptionsParameterA JDBC connection parameter. For more information, read Connection options.
        ValueThe value of the corresponding JDBC connection parameter.
        Source SchemaSelectSelect the schema for the source table. The special value, [Environment Default], will use the schema defined in the Matillion ETL environment.
        Source TableSelectThe table in your cloud data warehouse that you wish to output to Microsoft SQL Server.
        Target TableStringProvide a name for the output table that is to be created.
        Target SchemaStringThe schema for the target table.
        Load ColumnsColumn SelectUse 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 MaintenanceSelectDefine 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 KeyColumn SelectA column or a group of columns used to identify a row uniquely in a table.
        Update StrategySelectIn 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 TableSelectYes 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 WarningsSelectSpecify whether an output load should Continue or Fail if an ANSI Warning message is generated.
        Additional Copy OptionsSelectAdditional 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 SizeIntegerThe number of rows to load to the target between each COMMIT.

        Video

        What's Next