Home | Table of Contents | MSSQLDataWriter | CloverETL 4.6.1
--- | --- | ---
Prev | Writers | Next

## MSSQLDataWriter

# Not available in Community Designer

Short Description
Ports
Metadata
MSSQLDataWriter Attributes
Details
Examples
See also

### Short Description

MSSQLDataWriter loads data into MSSQL database.

Component | Data output | Input ports | Output ports | Transformation | Transf. required | Java | CTL | Auto-propagated metadata
--- | --- | --- | --- | --- | --- | --- | --- | ---
MSSQLDataWriter | database | 0-1 | 0-1 |  |  |  |  |

### Icon

### Ports

Port type | Number | Required | Description | Metadata
--- | --- | --- | --- | ---
Input | 0 | [1] | Records to be loaded into the database | Any
Output | 0 |  | For information about incorrect records | Input 0 (plus three Error Fields for MSSQLDataWriter
[1] If no file containing data for loading (Loader input file) is specified, the input port must be connected.

This component has one optional input port and one optional output port.

### Metadata

MSSQLDataWriter does not propagate metadata.

Metadata on the output port 0 contain three additional fields at their end: `number of incorrect row`, `number of incorrect column`, `error message`.

Table 49.4. Error Fields for MSSQLDataWriter

Field number | Field name | Data type | Description
--- | --- | --- | ---
LastInputField + 1 | <anyname1> | integer | Number of incorrect row
LastInputField + 2 | <anyname2> | integer | Number of incorrect column
LastInputField + 3 | <anyname3> | string | Error message

### MSSQLDataWriter Attributes

Attribute | Req | Description | Possible values
--- | --- | --- | ---
Basic
Path to bcp utility | yes | Name of bcp utility, including path. SQL Server Client Connectivity Components must be installed and configured on the same machine where Clover runs. Bcp command line tool must be available. |
Database | yes | Name of the database where the destination table or view resides. |
Server name |  | Name of the server to which bcp utility should connect. If bcp utility connects to local named or remote named instance of server, Server name should be set to `serverName\instanceName`. If bcp utility connects to local default or remote default instance of server, Server name should be set to `serverName`. If it is not set, bcp connects to the local default instance of server on localhost. The same meaning is true for the `serverName` which can be set in Parameters. However, if both Server name attribute and the `serverName` parameter are set, `serverName` in Parameters is ignored. |
Database table | [1] | Name of the destination table. |
Database view | [1] | Name of the destination view. All columns of the view must refer to the same table. |
Database schema |  | Owner of table or view. It does not need to be specified if the user performing the operations is the owner. If it is not set and the user is not the owner, SQL Server returns an error message and the process is cancelled. |
User name | yes | Login ID to be used when connecting to the server. The same can be set by specifying the value of the `userName` parameter in the Parameters attribute. If set, `userName` in Parameters is ignored. |
Password | yes | Password for the login ID to be used when connecting to the server. The same can be set by specifying the value of the `password` parameter in the Parameters attribute. If set, `password` in Parameters is ignored. |
Advanced
Column delimiter |  | Delimiter used for each column in data. Field values cannot have the delimiter within them. The same can be set by specifying the value of the `fieldTerminator` parameter in the Parameters attribute. If set, `fieldTerminator` in Parameters is ignored. | \t (default) | any other character
Loader input file | [2] | Name of input file to be loaded, including path. See Loader input file for more information. |
Parameters |  | All parameters that can be used as parameters by the bcp utility. These values are contained in a sequence of pairs of the following form: `key=value`, or `key` only (if the `key` value is the boolean `true`) separated from each other by semicolon. If the value of any parameter contains semicolon as its part, such value must be double quoted. See Parameters for more information. |
[1] One of these must be specified. [2] If input port is not connected, Loader input file must be specified and contain data. See Loader input file for more information.

### Details

MSSQLDataWriter loads data into a MSSQL database using the MSSQL database client. SQL Server Client Connectivity Components must be installed and configured on the same machine where CloverETL runs. The `bcp` command line tool must be available.

MSSQLDataWriter reads data through the input port or from a file. If the input port is not connected to any other component, data must be contained in file that should be specified in the component using Loader input file attribute.

If you connect some other component to the optional output port, it can serve to log the rejected records and information about errors. Metadata on this error port must have the same metadata fields as the input records plus three additional fields at its end: `number of incorrect row` (integer), `number of incorrect column` (integer), `error message` (string).

MSSQLDataWriter is a bulk loader suitable for uploading many records to database. To insert several records, you can also use DBOutputTable, which does not require `bcp` utility.

#### Loader input file

You can or you must specify another attribute (Loader input file), depending on an edge being connected to the input port. It is the name of input file with data to be loaded, including its path.

- If it is not set, a loader file is created in Clover or OS temporary directory (on Windows) or `named pipe` is used instead of temporary file (on Unix). The file is deleted after the load finishes.

- If it is set, specified file is created. It is not deleted after data is loaded and it is overwritten on each graph run.

- If an input port is not connected, the file must exist, must be specified and must contain data that should be loaded. It is not deleted nor overwritten.

#### Parameters

Parameters serve to pass additional parameters to `bcp` utility. All of the parameters must have the form of `key=value` or `key` only (if its value is `true`). Individual parameters must be separated from each other by colon, semicolon or pipe. Note that colon, semicolon or pipe can also be a part of some parameter value, but in this case the value must be double quoted.

Among the optional parameters, you can also set `userName`, `password` or `fieldTerminator` for User name, Password or Column delimiter attributes, respectively. If some of the three attributes (User name, Password and Column delimiter) will be set, corresponding parameter value will be ignored.

If you also need to specify the server name, you should do it within parameters. The pattern is as follows: `serverName=[msServerHost]:[msServerPort]`. For example, you can specify both server name and user name in the following way: `serverName=msDbServer:1433|userName=msUser`.

#### Notes and Limitations

MSSQLDataWriter does not write lists and maps.

### Examples

#### Loading data to MSSQL database

Load records (productID, amount) to table `products` of MSSQL database `sales` on `mssql.example.com`. Database schema is `dbo`, username is `smithj` and password is `smithy`.

##### Solution

Install `bcp` utility.

Set up the following attributes of MSSQLDataWriter.

Attribute | Value
--- | ---
Path to bcp utility | C:/Program Files/Microsoft SQL Server/Client SDK/ODBC/110/Tools/Binn/bcp.exe
Database | sales
Server name | mssql.example.com
Database table | products
Database schema | dbo
User | smithj
Password | smithy

In case the server uses a non-standard port number, append the port number after Server name.

    mssql.example.com,1437

The port number is separated by a comma, not by a semicolon.

### Troubleshooting

To test that the connection to database works, you can read some database table with `bcp` utility directly:

    bcp [database_name].[database_schema].[tablename] out outputfile -U user -S serverHostName,port -c

e.g.

    bcp master.dbo.products out products.txt -U doejohn -S mssql.example.com,1435 -c

You can use the utility to check that you can insert records into database table:

    bcp [database_name].[database_schema].[tablename] in inputfile -U user -S serverHostName,port -c

e.g.

    bcp master.dbo.products in inputfile.txt -U doejohn -S mssql.example.com,1435 -c

The `inputfile.txt` should be a tab-delimited csv file.

See also documentation on bcp.

### See also

DBOutputTable
Common Properties of Components
Specific Attribute Types
Common Properties of Writers
Writers Comparison

Prev | Up | Next
MongoDBWriter | Home | Table of Contents | MySQLDataWriter