SQL Server Update Writer

Updating data on an SQL Server database

In order to update data on an SQL Server database, use the following target definition:

<Target config="[configuration]">sqlserver.update://table_name(key_field1, key_field2,...)</Target>

The SQL Server Update Writer will attempt to update the records defined by the table_name (the name of the table to update) and the key_field values. These values will be used in a WHERE clause for the UPDATE statement.

For the configuration, there are two possibilities:

• Using a connection string directly
• Using a connection string which is located in a text file

Using a connection string directly

Pass the connection string directly into the configuration of the source tag:

<Target config="Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;">sqlserver.update://CreditStatements(StatementId)</Target>

Parameters can be used inside the connection string, if needed.

Please note the use the substitution of the greater (>, >) operator in the SQL query (needed for XML compliance).

Using a connection string inside a text file

In order to read the connection string from a file, use the following syntax (using the @ character):

<Target config="@sqlserver_connection.txt">sqlserver.update://Contacts(ContactID, Revision)</Target>

Sample content of the sqlserver_connection.txt file, also using a parameter (§env§):

Server=§env§;Database=myDataBase-§env§;User Id=myUsername;Password=myPassword;

Sample Connection Strings

See https://www.connectionstrings.com/sql-server/