This topic describes the data types and parameters supported by SQL Server Writer and how to configure it by using the codeless user interface (UI) and code editor.

SQL Server Writer allows you to write data to tables stored in primary SQL Server databases. Specifically, SQL Server Writer connects to a remote SQL Server database through Java Database Connectivity (JDBC), and runs an INSERT INTO statement to write data to the SQL Server database. Internally, data is submitted to the database in batches.

Note You must configure a connection before configuring SQL Server Writer. For more information, see Configure an SQL Server connection.

SQL Server Writer is designed for extract-transform-load (ETL) developers to import data from data warehouses to SQL Server databases. SQL Server Writer can also be used as a data migration tool by users such as database administrators (DBAs).

SQL Server Writer obtains data from a Data Integration reader, and writes the data to the destination database by running the INSERT INTO statement. If a primary key conflict or unique index conflict occurs, data cannot be written to the conflicting rows. To improve performance, SQL Server Writer makes batch updates with the PreparedStatement method and sets the rewriteBatchedStatements parameter to true. In this way, SQL Server Writer buffers data, and submits a write request when the amount of data in the buffer reaches a specific threshold.

Note
  • Data can be written only to tables stored in the primary SQL Server database.
  • A sync node that uses SQL Server Writer must have at least the permission to run the INSERT INTO statement. Whether other permissions are required depends on the SQL statements specified in the preSql and postSql parameters when you configure the node.

Data types

SQL Server Writer supports most SQL Server data types. Make sure that your data types are supported.

The following table lists the data types supported by SQL Server Writer.
Category SQL Server data type
Integer BIGINT, INT, SMALLINT, and TINYINT
Floating point FLOAT, DECIMAL, REAL, and NUMERIC
String CHAR, NCHAR, NTEXT, NVARCHAR, TEXT, VARCHAR, NVARCHAR (MAX), and VARCHAR (MAX)
Date and time DATE, TIME, and DATETIME
Boolean BIT
Binary BINARY, VARBINARY, VARBINARY (MAX), and TIMESTAMP

Parameters

Parameter Description Required Default value
datasource The connection name. It must be identical to the name of the added connection. You can add connections in the code editor. Yes None
table The name of the destination table. Yes None
column The columns in the destination table to which data is written. Separate the columns with a comma (,). Example: "column":["id","name","age"]. Set the value to an asterisk (*) if data is written to all the columns in the destination table. That is, set the column parameter as follows: "column":["*"]. Yes None
preSql The SQL statement to run before the sync node is run. For example, you can clear outdated data before data synchronization. Currently, you can run only one SQL statement on the codeless UI, and multiple SQL statements in the code editor. No None
postSql The SQL statement to run after the sync node is run. For example, you can add a timestamp after data synchronization. Currently, you can run only one SQL statement on the codeless UI, and multiple SQL statements in the code editor. No None
writeMode The write mode. Valid value: insert. When a data record violates the primary key constraint or unique index constraint, Data Integration considers it dirty and retains the original data. No insert
batchSize The number of data records to write at a time. Setting this parameter can greatly reduce the interactions between Data Integration and the SQL Server database over the network, and increase the throughput. However, an excessively large value may lead to the out of memory (OOM) error during the data synchronization process. No 1024

Configure SQL Server Writer by using the codeless UI

  1. Configure the connections.
    Configure the source and destination connections for the sync node.
    Parameter Description
    Connection The datasource parameter in the preceding parameter description. Select a connection type, and enter the name of a connection that has been configured in DataWorks.
    Table The table parameter in the preceding parameter description.
    Statement Run Before Writing The preSql parameter in the preceding parameter description. Enter an SQL statement to run before the sync node is run.
    Statement Run After Writing The postSql parameter in the preceding parameter description. Enter an SQL statement to run after the sync node is run.
    Solution to Primary Key Violation The writeMode parameter in the preceding parameter description. Select the expected write mode.
  2. Configure field mapping, that is, the column parameter in the preceding parameter description.
    Fields in the source table on the left have a one-to-one mapping with fields in the destination table on the right. You can click Add to add a field, or move the pointer over a field and click the Delete icon to delete the field.
    Parameter Description
    Map Fields with the Same Name Click Map Fields with the Same Name to establish a mapping between fields with the same name. Note that the data types of the fields must match.
    Map Fields in the Same Line Click Map Fields in the Same Line to establish a mapping for fields in the same row. Note that the data types of the fields must match.
    Delete All Mappings Click Delete All Mappings to remove mappings that have been established.
    Auto Layout Click Auto Layout. The fields are automatically sorted based on specified rules.
    Change Fields Click the Change Fields icon. In the Change Fields dialog box that appears, you can manually edit fields in the source table. Each field occupies a row. The first and the last blank rows are included, whereas other blank rows are ignored.
    Add
    • Click Add to add a field. You can enter constants. Each constant must be enclosed in single quotation marks (' '), such as 'abc' and '123'.
    • You can use scheduling parameters, such as ${bizdate}.
    • You can enter functions supported by relational databases, such as now() and count(1).
    • Fields that cannot be parsed are indicated by Unidentified.
  3. Configure channel control policies.
    Parameter Description
    Expected Maximum Concurrency The maximum number of concurrent threads to read and write data to data storage within the sync node. You can configure the concurrency for a node on the codeless UI.
    Bandwidth Throttling Specifies whether to enable bandwidth throttling. You can enable bandwidth throttling and set a maximum transmission rate to avoid heavy read workload of the source. We recommend that you enable bandwidth throttling and set the maximum transmission rate to a proper value.
    Dirty Data Records Allowed The maximum number of dirty data records allowed.
    Resource Group The servers on which nodes are run. If an excessively large number of nodes are run on the default resource group, some nodes may be delayed due to insufficient resources. In this case, we recommend that you purchase an exclusive resource group for data integration or add a custom resource group. For more information, see DataWorks exclusive resources and Add a custom resource group.

Configure SQL Server Writer by using the code editor

In the following code, a node is configured to write data to an SQL Server database. For more information about the parameters, see the preceding parameter description.

{
    "type":"job",
    "version":"2.0",// The version number.
    "steps":[
        {
            "stepType":"stream",
            "parameter":{},
            "name":"Reader",
            "category":"reader"
        },
        {
            "stepType":"sqlserver",// The writer type.
            "parameter":{
                "postSql":[],// The SQL statement to run after the sync node is run.
                "datasource":"",// The connection name.
                "column":[// The columns to which data is written.
                    "id",
                    "name"
                ],
                "table":"",// The name of the destination table.
                "preSql":[]// The SQL statement to run before the sync node is run.
            },
            "name":"Writer",
            "category":"writer"
        }
    ],
    "setting":{
        "errorLimit":{
            "record":"0"// The maximum number of dirty data records allowed.
        },
        "speed":{
            "throttle":false,// Specifies whether to enable bandwidth throttling. A value of false indicates that the bandwidth is not throttled. A value of true indicates that the bandwidth is throttled. The maximum transmission rate takes effect only if you set this parameter to true.
            "concurrent":1,// The maximum number of concurrent threads.
        }
    },
    "order":{
        "hops":[
            {
                "from":"Reader",
                "to":"Writer"
            }
        ]
    }
}