Integration - Microsoft SQL
The Integration - SQL Plugin allows Scripting to connect to an external MS SQL database, and link Workflow Fields with columns held within the table. Any linked Fields are automatically populated from the paired column on Workflow pop, and any changes made to the Fields are written back to the database on Workflow completion.
How To
The Integration - SQL Plugin needs to be enabled in the
Plugins section of the Admin module, with the relevant MS SQL server's account details entered into the page. Once the details have been entered, click the Save button, and then Activate.
Once the Integration - SQL Plugin is active, Workflows can be configured to link them to the table through the
External Links section of the
Workflow Properties screen for the desired Workflow.
When the External Links section has been expanded, the Manage button can be clicked to set up an External Link. Select the Integration - SQL Plugin from the first list, and then point to the desired database and table in the SQL server specified in the Plugin configuration. Once these have been set and the Add button clicked, the link will appear in the right-hand portion of the window, and the reference column can be set. The reference column acts as the record key for working out which record row to link to the Fields during a Workflow run.
In this example, if a new record with reference of 101 was generated, the SQL Plugin would search tbl_CustomerData for the value 101 in the customerId column, and then make the remaining column values for that row available to linked Fields within the Workflow.
After customising the External Link as desired, close the window and then click Save at the bottom of the Workflow Properties page. Enter the
Workflow Designer view, and then Edit a Field on the page that you want to link with the SQL database. In the
Field Properties window, the column that you wish to link with the Field can be chosen. In this example, the value in the record's
customerAge column will populate the Field on Workflow pop, and any changes made during the Workflow run will be written back to the
customerAge column.
Popping
The Workflow can be popped by a variety of methods, please refer to
Popping Mechanisms for further information.
When all Fields have been linked as desired, then when the Workflow is popped a check is made in the SQL Table against the pop reference (if provided):
-
If a record is found with this reference, then any Fields that are linked will be automatically populated with that record's data, and any alteration to those Fields will be written back to the record at the end of the Workflow run.
-
If no record is found with the reference, then no data is populated in the Workflow, but the reference is used in generating a new record at the end of the Workflow run.
-
If no reference is provided, then no data is populated in the Workflow, and a new record is generated at the end of the Workflow run with an automatically-generated reference (of the form
CSAUTO-#-#-#).
Notes
The account details used when setting up the Integration - SQL Plugin need to have read/write access to the relevant SQL tables you intend to use.
The reference column in the SQL Table needs to be of type nvarchar(max) by default, to allow Scripting to write new records to it if no reference is specified. If a reference is always specified, then the column can be of any compliant type.
The reference of a popped Workflow can be changed by altering the value of [var_csReference] inside the Workflow. This needs to comply with the reference column datatype, or an error may occur.
When a Workflow is linked to a database table using this Plugin, a Campaign is automatically created with the Campaign Name being the name of the SQL table that it is linked to
