Provides an efficient way for executing bulk insert/update on a database without having to hand-write INSERT or UPDATE statements.

Revision History Initial Release Added support for connecting via an SSH tunnel Fixed bug on SSH. Changed Node to have the private key in the Connection



Type: Connection Input
The Connection Property

Type: String

Type: String

Type: String

Type: Password

Type: String
The name or IP address of the SSH server.

Type: Number
The port through which to connect to the SSH Server.

Database Port
Type: Number
The port on which the MySQL Database is running.

SSH Username
Type: String
The username with which to authenticate to the SSH Server.

SSH Password
Type: Password
The password with which to authenticate to the SSH Server. Provider either this or the SSH Key File.

SSH KeyFile
Type: String
The Private Key File contents with which to authenticate to the SSH Server. Provider either this or the SSH Password. If using this, the node must be run on a drop point.

Note the SSH.NET library is used, and it does not support the aes256-ctr cypher (Issue)

ssh-keygen -f "C:\temp\sshkey.pem" -p -Z aes256-cbc

SSH KeyFile Pass Phrase
Type: Password
The pass phrase (if any) with which the SSH Key File has been locked.


Type: Xml Input
An XML representation of the data to be updated or inserted (see Remarks).


Type: String Input
A comma-separated list of fields that represent key fields for the update or insert operation. These values will be matched in the target table to determine whether the row already exists. The key fields listed in this property must be supplied in the TableXml  property. It is not necessary to supply key fields if AllowUpdate  is false .


Type: Boolean Input
Indicates whether inserts are allowed into the target table.


Type: Boolean Input
Indicates whether updates are allowed into the target table (requires at least one field to be specified in KeyFields ).


Type: Int32 Output
Returns the number of rows affected by the insert/update operation.


The XML supplied should contain the following paths root\tablename\columnname . The name of the root node is not important. The second tier should contain the table name and only a single table can be updated per call. The third tier should contain the column name.

Here's an example of a payload that will add or insert two rows into a table called inventory :

    <description>first inventory item</description>
    <description>second inventory item</description>

When AllowUpdate  is true , the fields specified in KeyFields  will be matched from TableXml  to the target table. All KeyFields  supplied must exist in both TableXml  and the target table. Multiple key fields should be separated with commas. Note that the specified KeyFields  do not have to be the database primary key fields but should indicate the key fields for purposes of the update operation.

For example, if an inventory table contains the fields id (int), code (varchar(50)), description (varchar(100)) , the KeyFields  property could be set to code  if it was necessary to update based on this value rather than the internal integer id  field.

If you need to integrate with a non-Microsoft SQL database, refer to Integrating with SQL Databases for additional information and connection strings.


The most common error that occurs is "Failed to convert parameter value from a String to a Double" (or other numeric type). This occurs due to the Regional Settings in place on the machine on which the DropPoint is running. For example, if the decimal separator is set to a comma, only numbers containing a comma decimal separator will be parsed correctly.

To resolve these problems, first determine exactly which field or fields are not being converted correctly by progressively reducing your XML payload until it contains only the problem fields. Next, review the regional settings at the DropPoint.

If it is not possible to change Regional Settings on the machine, the numeric fields need to be formatted in a way that conforms to the Regional Settings.


See for an example.

See Also

Did this answer your question?