Databases

Data Transformation

ThinkAutomation can be used to transfer data from one database to another - and to transform data during the transfer.

For example, we can create a ThinkAutomation Message Source to read data from a database. In this example we are reading from the AdventureWorks sample SQL Server database using the query:

SELECT * FROM Person.Person WHERE BusinessEntityID > @ID    

The parameter @ID is set to the built-in ThinkAutomation field %LastDatabaseId%. The %LastDatabaseId%value is set to a column value from the source query after each record - in this case BusinessEntityId. By doing this we improve performance - since each time ThinkAutomation reads records from the database it only has to read new records since the last query.

When you setup a Database Message Source the Test button allows you to preview the query results. It also can optionally create a new Automation with Extract Field actions automatically created for each column returned.

When the ThinkAutomation Message Source reads records from a database it creates as new message for each new row returned. The %Msg_Body% is set to Json. For example:

{
  "BusinessEntityID": 1,
  "PersonType": "EM",
  "NameStyle": false,
  "Title": "",
  "FirstName": "Ken",
  "MiddleName": "J",
  "LastName": "Sánchez",
  "Suffix": "",
  "EmailPromotion": 0,
  "AdditionalContactInfo": "",
  "Demographics": "<IndividualSurvey xmlns=\"http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey\"><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>",
  "rowguid": "92c4279f-1207-48a3-8448-4636514eb7e2",
  "ModifiedDate": "2009-01-07"
}

If we used the Test button on the Message Source then the new Automation would default to:

AdventureWorks Automation

// Automation Actions Follow

BusinessEntityID=Extract FieldFrom%msg_body%Json Path"BusinessEntityID"

PersonType=Extract FieldFrom%msg_body%Json Path"PersonType"

NameStyle=Extract FieldFrom%msg_body%Json Path"NameStyle"

Title=Extract FieldFrom%msg_body%Json Path"Title"

FirstName=Extract FieldFrom%msg_body%Json Path"FirstName"

MiddleName=Extract FieldFrom%msg_body%Json Path"MiddleName"

LastName=Extract FieldFrom%msg_body%Json Path"LastName"

Suffix=Extract FieldFrom%msg_body%Json Path"Suffix"

EmailPromotion=Extract FieldFrom%msg_body%Json Path"EmailPromotion"

AdditionalContactInfo=Extract FieldFrom%msg_body%Json Path"AdditionalContactInfo"

Demographics=Extract FieldFrom%msg_body%Json Path"Demographics"

rowguid=Extract FieldFrom%msg_body%Json Path"rowguid"

ModifiedDate=Extract FieldFrom%msg_body%Json Path"ModifiedDate"

Now we have extracted fields for each column.

If you wanted to simply insert/update this data into another database - you could add the database Table Name/Column Name to each extracted field (on the Database Update) tab. Then use the Update A Database action.

You could use the Update A Database Using Custom SQL action to perform a custom select > insert/update - and add additional data or modify values (using the Set action).

The Automation will then execute whenever new rows are returned from the Message Source query.

---

Transfer On-Premise Data To MongoDB or Azure Cosmos Cloud Database

When using the Database Message Source type, the %Msg_Body% is set to Json representing the row data. This makes it simple to then store this data in a document DB - such as MongoDB or Azure Cosmos.

If you wanted to change the Json - you can use the Create Json action to create custom Json text and assign fields or variables to each Json path.

AdventureWorks Automation

// Extract from incoming data

BusinessEntityID=Extract FieldFrom%msg_body%Json Path"BusinessEntityID"

PersonType=Extract FieldFrom%msg_body%Json Path"PersonType"

NameStyle=Extract FieldFrom%msg_body%Json Path"NameStyle"

Title=Extract FieldFrom%msg_body%Json Path"Title"

FirstName=Extract FieldFrom%msg_body%Json Path"FirstName"

MiddleName=Extract FieldFrom%msg_body%Json Path"MiddleName"

LastName=Extract FieldFrom%msg_body%Json Path"LastName"

Suffix=Extract FieldFrom%msg_body%Json Path"Suffix"

EmailPromotion=Extract FieldFrom%msg_body%Json Path"EmailPromotion"

AdditionalContactInfo=Extract FieldFrom%msg_body%Json Path"AdditionalContactInfo"

Demographics=Extract FieldFrom%msg_body%Json Path"Demographics"

rowguid=Extract FieldFrom%msg_body%Json Path"rowguid"

ModifiedDate=Extract FieldFrom%msg_body%Json Path"ModifiedDate"

 

If%PersonType%Is BlankThen

PersonType=E

End If

 

// Change the Person Type to match our format

Select Case%PersonType%

Case=FT

PersonType=F

Case=EM

PersonType=P

Case Else

PersonType=E

End Select

 

// Change to our format and backup to MongoDB

BackupJson=

BackupJson=Create JSON{ "PersonId": "<Number>%BusinessEntityID%", "PersonType": "%PersonType%", "FirstName": "%FirstName%", "LastName": "%LastName%" }

Update MongoDBDatabase BackupCollectionPersonsUpsert%BackupJson%Query{ "PersonId" : "%BusinessEntityID%" }

---