MSSQLDataWriter
We assume that you have already learned what is described in:
If you want to find the right Writer for your purposes, see Writers Comparison.
Short Summary
MSSQLDataWriter loads data into MSSQL database.
| Component | Data output | Input ports | Output ports | Transformation | Transf. required | Java | CTL |
|---|---|---|---|---|---|---|---|
| MSSQLDataWriter | database | 0-1 | 0-1 | no | no | no | no |
Abstract
MSSQLDataWriter loads data into a database using the MSSQL database client. It 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 another file that should be specified in the component.
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).
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.
Icon
Ports
| Port type | Number | Required | Description | Metadata |
|---|---|---|---|---|
| Input | 0 | 1) | Records to be loaded into the database | Any |
| Output | 0 | no | For information about incorrect records | Input 0 (plus three Error Fields for MSSQLDataWriter2)) |
Legend:
1): If no file containing data for loading (Loader input file) is specified, the input port must be connected.
2): Metadata on the output port 0 contain three additional
fields at their end: number of incorrect row,
number of incorrect column, error
message.
Table 46.3. 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 owner | Owner of table or view. 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 | 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. | ||
Legend:
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.
Advanced Description
Loader input file
You can or you must specify another attribute (Loader input file), dependent 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 pipeis 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
You may also want to set some series of parameters that can be
used when working with MSSQL (Parameters). For
example, you can set the number of the port, etc. 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.