The SQL Destination Step allows you to Insert, Update, Upsert, run Commands and Stored Procedures against a Microsoft SQL Server database.
Basic Configuration
SQL Server Steps can use either a direct connection (see Connections and SQL Connections) or a Relay (see Relays). Either one needs to be selected to use the Step.
Operation Types
You can send Records to SQL Server in a variety of different ways using different operation types:
Insert
The Insert Operation inserts Records using INSERT INTO commands.
The object name will be the Table Name. The Mappings will map a record path (including the record type signifier e.g. record.field) to the name column in the table and the required SQL type.
Update
The Update Operation updates existing Records using UPDATE SET commands.
The object name will be the Table Name. The Mappings will map a record path (including the record type signifier e.g. record.field) to the name column in the table and the required SQL type.
The Key attribute specifies which value is used to lookup on which table column to identify the database record to update.
Upsert
The Upsert Operation will update an existing database record if it exists, or insert a new one if not.
The object name will be the Table Name. The Mappings will map a record path (including the record type signifier e.g. record.field) to the name column in the table and the required SQL type.
The Key attribute specifies which value is used to look up on a table column to identify the database record to update.
Command
The Command Operation runs a SQL command for each Record being processed.
The Input Parameters for the command work slightly differently as dictated by TSQL syntax. In the SQL command, a variable @parameter is used as per TSQL syntax.
Each of these parameters needs to be mapped using the Input Parameters options:
In this case, the Name (excluding the @) and SQL type of the parameter are specified and a value for that parameter. If you need to use a value from the current record, you can use {@} parameter syntax or {{}} handlebars syntax to specify it. The reason for this departure is to cater to constants and other calculated values.
Stored Procedure
The Stored Procedure Operation will call a stored procedure for each record being processed.
The stored procedure to run is specified in the command value with the required parameters using TSQL @parameter syntax with the linking specified in the Input Parameters as with the Command Operation:
On-Premise Using Relays
You can simply switch the operation from SQL Servers accessible on the internet to on-premise solutions using Relays with no configuration changes to operations. Only the connection has to be changed to a Relay.
Versions Supported
The following SQL Server versions are supported:
TDS 7.4 (SQL Server 2012/2014/2016/2017)
TDS 7.3.B (SQL Server 2008 R2)
TDS 7.3.A (SQL Server 2008)
TDS 7.2 (SQL Server 2005)
TDS 7.1 (SQL Server 2000)