Microsoft SQL Actions

Actions are the operations (or API operations) that you can perform on Microsoft SQL database application.

You can simply drag and drop elements and values from input trigger, derived values using formulas or desired constant that you may wish to map with Microsoft SQL.

You can automate the Microsoft SQL query executions which you do manually otherwise.

As shown below in Figure 1, you can Create, Update, Delete, and Upsert records in database tables based on response of the trigger event.

Figure 1. Action for Microsoft SQL Application on Connect iPaaS

Create Record

Create records in the selected database table when an event triggers.

• Select the table from the database in which you have to create a record. After the table selection, all the Not Null columns of your tables will be shown as required fields.
• Add optional fields of the table using the + button. You can map field data using the data coming from the trigger or use excel functions. You can also use the evaluate functionality to map values for enum type columns.

For example in below Figure 2, values of multiple columns of Microsoft SQL are mapped to attributes from ServiceNow.

Figure 2. Microsoft SQL Create Record Action

Delete Record

Delete one or multiple Microsoft SQL database records in response to the event trigger. You can filter records based on the selection criteria.

• Select the table in your database from which you want to delete the records.
• Use the Choose where condition to add the conditions for selecting the records to be deleted. You can construct complex logic of AND and OR conditions to select the records.

For example, in Figure 3 below, from the table Incident all the records which have incident name same as category of ServiceNow incident and incident_state equal to State of the incident will be deleted when an incident is updated in ServiceNow (Privileged).

Figure 3. Microsoft SQL Delete Record Action

Update Record

Update one or multiple Microsoft SQL database records in response to an event trigger. You can update the record from the table based on some condition.

• Select the table in your database which you want to update.
• Add the conditions to select the records that will be updated. You can construct the logic of conditions using complex conditional OR and AND.
• Select the updates that you want to perform on the selected records. You can use data Mapping and Transformation and excel functions. You can also use the evaluate functionality to map values for enum type columns.

In the figure 4 below, whenever an Incident is updated on ServiceNow (Privileged), a record is updated in Microsoft SQL database.

All the records in the Incident table where value ofIncident_name is equal to the value of Category from the incoming ServiceNow incident or the value of incident_description column is same as the short description of the incident update on ServiceNow, will be updated with the following changes.

• The time_of_incident is updated to current time using the excel style now() function.
• The incident_state column is updated to the value of Incident State from the incoming event of ServiceNow.

Figure 4. Microsoft SQL Update Record Action

Upsert Record

Upsert operation is the combination of Update and Create (insert) operations. Connect iPaaS will find if a record with matching Primary Key is present in the table. If a record is found, the record will be updated with the data provided. If a record is not found, Connect iPaaS will create a new record with the data provided. Primary key can either be a single column or a combination of columns.

The other columns of the table are mapped with different attributes of the updated incident in ServiceNow.

The time_of_incident column of database table is filled by the now() function from the excel style functions.

Figure 5. Microsoft SQL Upsert Record Action