File-based data - Insert JSON data in SQL-Database¶

Overview¶

This Scenario describes step by step how JSON-data can be inserted into an SQL database with the SMARTUNIFIER.

Prerequisite¶

JSON-file

{
 "equipmentId": "Equipment_A1234",
 "orderNr": "Order_000101",
 "materialNr": "A1C55100",
 "quality": "IO"
}

SQL-Server Database

Create Database Command: CREATE DATABASE unifier

Create Schema Command: CREATE SCHEMA dbo

Create Table Command: create table dbo.SU_DEMO_UC1_TABLE(EQUIPMENT_ID varchar(max), ORDER_NR varchar(max), MATERIAL_NR varchar(max), QUALITY varchar(max))

Or any other SQL Database supported by SMARTUNIFIER.

Information Model¶

Information Model - JSON-file¶

Create an Information Model that represents the structure of the JSON-file

Structure of the JSONFile Information Model:

Event that represents the trigger for the Mapping
Variables under the Event represent the key-value pairs from the JSON-file

Information Model - SQL-Database¶

Create an Information Model that represents the database

Structure of the Database Information Model:

Event that represents the table of the database
Variables under the Event represent the columns within the table

Communication Channel¶

Communication Channel - JSON-file¶

In this scenario the JSON-file is processed by the SMARTUNIFIER with the build-in File Consumer

Create File Consumer Channel:

Select the JSONFile Information Model created previously
Select File reader (JSON) as Channel Type

Configuration:

Specify paths to following folder:
- InFolder
- ProcessFolder
- OutFolder
- ErrorFolder

Select the Event to configure the FileNameFilter

Communication Channel - SQL Database¶

In this scenario the JSON-file is processed by the SMARTUNIFIER with the build-in File Consumer.

Create Database Channel:

Select the Database Information Model created previously
Select SqlDatabase as Channel Type

Configuration:

Select the root node of the Information Model and configure the database access
- Select SQLServer as Type
- Enter the JDBC Url, according to the selected database type - jdbc:sqlserver://192.168.0.111:1433;databaseName=unifier
- Enter username and password of the database

Select the Event node in the Information Model and configure the table settings
- Enable the checkbox Insert
- Enter the name for the Table as well as the Schema

Select the Variable node EquipmentId in the Information Model and configure the columns (Repeat this step with the rest of the Variables)
- Enable the checkbox AssignDatabaseColumn
- Enter the name for the Column

Mapping¶

Create a new Mapping with the Information Models created previously (JSONFile and Database).

Create a Rule that handles the assignment of values from the JSON-file to the database.

Enter a Rule Name
Drag and Drop the File Event into the Trigger field
Drag and Drop the DatabaseInsertEvent into the Actions panel
Assign source to target (Repeat for all Variables)
Drag and Drop the Variable equipmentId from the json model into the according Source field