Skip to main content

Transco V2

motivation

Integration scenarios often need to 'promote' or 'enrich' messages/data from external stored systems. Codit is a long-term implementer of something called 'Transco', which was previously a BizTalk component, but is now been required in modern Azure Logic Apps integrations.

Invictus provides a Transco component to promote properties from a database, by using the content or context to create the SQL query which will be executed against the specified table. Transco can also perform transformations on XML content by simply specifying the XSLT file from storage (Transco supports XSLT 1.0 syntax.).

Available endpoints​

  • /api/TranscoXML: used with XML content. A Transco config file is used to list the instructions necessary to promote values from an SQL database or to transform the content via an XSLT file.

  • /api/TranscoJson: used with JSON content. A Transco config file is used to list the instructions necessary to promote values from an SQL database. Transformations cannot be performed on JSON content.

  • /api/MatrixBasicPromote: accepts a simple list of parameters and promotes them to the Context in the response.

The Transco request requires following values:

  • XML/JSON content in BASE 64 format;
  • Context as key-value pair list;
  • Name of Transco config file in Azure Blob Storage.
Full request body JSON example
// POST /api/TranscoXML
// POST /api/TranscoJson
{
  "Content": "PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0idXRmLTE2Ij8+PHJvb3Q+PG5hbWUgc3RhdHVzPSJNZW1iZXIiPkpvaG4gRG9lPC9uYW1lPjwvcm9vdD4=",
  "Context": {
    "CustomerActive": "true" 
  }, 
  "TranscoConfig": "docs_config.json"
}
Full response body JSON example
// 200 OK <- /api/TranscoXML
// 200 OK <- /api/TranscoJson
{
  "Content": "PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0idXRmLTE2Ij8+PE1lbWJlcj5Kb2huIERvZTwvTWVtYmVyPg==",
  "Context": {
    "CustomerActive": "true"
  },
  "TranscoConfig": "docs_config.json"
}

Transco config file​

A JSON Transco config file is required to specify details about the instruction which will be performed. Instructions are executed in the order in which they appear. The name of the config file should be specified in the request so that it can be retrieved from the storage account.

Transco can execute SQL commands to promote properties from a specified database and table. The connection to the database can be achieved via either a raw connection string or the name of an Azure Key Vault secret.

The parameters of the SQL query can be populated from 3 sources:

  • XML/JSON request content via XPath/JPath
  • Request context
  • Fixed value

The SQL query result will inserted at the specified destination XPath or JPath. For XPaths ending at an attribute, e.g. /persons/person/@name, the value will be inserted in that attribute. Otherwise, it will be inserted in the inner text.

  • A scopePath can be defined so that the command affects only nodes within that path.
  • A connection to the SQL database must be provided (via a connection string in plain text or in an Azure Key Vault secret).
  • Parameters in the SQL query must be denoted with an @ symbol. Parameters may be given a name or indexed with a number. 
    SELECT CustomerStatus FROM dbo.Customers WHERE CustomerName = @Name AND Active = @Active*
    -- or
    SELECT CustomerStatus FROM dbo.Customers WHERE CustomerName = @1 AND Active = @2*
storage account

All required files are to be saved in an Azure Storage Account and in a Blob Storage container with the name: Transcov2configstore (This container is automatically created by Invictus):

  • /Configs folder with Transco config files
  • /XSLTs folder with transformation files
  • /Assemblies folder with assembly and dependency DLL files
Full Transco config file specification
{
  "instructions": [
    {
      "scopePath": [XPath/JPath of content scope],
      "namespaces": [
        {
          "namespace": [XML Namespace],
          "prefix": [XML Namespace prefix]
        }
      ],
      "destination": [XPath/JPath of the results destination, or Context key if promoteToContext = true],
      "promoteToContext": [If true query result is saved to Context at key destination],
      "command": {
        "databaseConnectionString":[Raw connection string to DB],
         "databaseKeyVaultName": [Name of DB connection string secret in Key Vault],
         "commandValue": [SQL query to be executed],
         "isMandatory": [If true, will throw error when result is null],
        "columnName": [Obtain value from specified column if query returns multiple fields. If empty, value from first column is obtained],
         "defaultValue": [Default value of result],
         "parameters": [
           {
            "paramName": [Name of param in query],
             "value": [Type dependent. XPath or JPath if valueType = "path"],
             "type": [SQL DB type. See: https://learn.microsoft.com/en-us/dotnet/api/system.data.sqldbtype],
             "valueType": ["path" or "fixedValue" or "context"]
          },
          {
            "paramName": [Name of param in query],
            "value": [Type dependent. Any string value if valueType = "fixedValue"],
            "type": [SQL DB type. See: https://learn.microsoft.com/en-us/dotnet/api/system.data.sqldbtype],
            "valueType": ["path" or "fixedValue" or "context"]
          },
          {
            "paramName": [Name of param in query],
            "value": [Type dependent. Key to value in context if valueType = "context"],
            "type": [SQL DB type. See: https://learn.microsoft.com/en-us/dotnet/api/system.data.sqldbtype],
            "valueType": ["path" or "fixedValue" or "context"]
          }
        ],
        "cache": {
          "useCaching": [If true, query result is cached],
          "cachingTimeout": [Cache timeout timespan]
        }
      }
    },
    {
      "xsltTransform": [Name of XSLT file],
      "extensions": [
        {
          "namespace": [Assembly namespace],
           "assemblyName": [Assembly name],
           "className": [Assembly class name],
           "dependencies": [
             "[DLL dependency file name]",
             "[DLL dependency file name]"
           ]
        }
      ]
    }
  ],
  "options": {
    "configCache": {
      "useCaching": [If true, config file is cached],
      "cachingTimeout": [Cache timeout timespan]
    },
    "indentResult": [If true, Transco results will be formatted and indented]
  }
 }

Migrating Transco v1 to v2​

Migrating Transco v1 to v2

We need to change the authentication and endpoint and remove the metadata links.

Also we need to change the Transco configuration files, for this you can use the migration tool from Codit's integration practice that can be found here.

"Transform_XML": {
  "type": "Http",
  "inputs": {
    "method": "POST",
-   "uri": "[parameters('invictus').Framework.Transco.v1.TranscoXmlUrl]",
+   "uri": "[parameters('invictus').Framework.Transco.v2.TranscoXmlUrl]",
    "authentication": {
-     "username": "Invictus",
-     "password": "@parameters('invictusPassword')",
-     "type": "Basic",
+     "identity": "[parameters('infra').managedIdentity.id]",
+     "audience": "[parameters('invictus').authentication.audience]",
+     "type": "ManagedServiceIdentity"
    },
    "body": {
      "Content": "@triggerBody()?['Content']",
      "Context": "",
      "TranscoConfig": "EFACT_D96A_ORDERS-to-Generic_Order.json"
    }
  },
  "runAfter": {}
}

Migrating Matrix v1 to Transco v2

The Matrix component's functionality has been placed in the Transco v2 API.

"Extract_Message_Context": {
  "type": "Http",
  "inputs": {
    "method": "POST",
-   "uri": "[parameters('invictus').framework.matrix.v1.basicMatrixUrl]",
+   "uri": "[parameters('invictus').framework.Transco.v2.basicMatrixUrl]",
    "body": {
      "Domain": "B2B-Gateway",
      "Service": "@{concat('AS2-Receive-', body('Decode_AS2_message')?.aS2Message?.aS2To)}",
      "Action": "@{outputs('Integration_Account_Artifact_Lookup_-_Get_SendingPartner')?.properties?.metadata?.PayloadFormat}",
      "Version": "1.0",
      "Sender": "@{outputs('Integration_Account_Artifact_Lookup_-_Get_SendingPartner')?.properties?.metadata?.PartyName}",
      "Content": "@{base64(body('Decode_AS2_message')?.AS2Message?.Content)}",
      "KeyValueCollection": {
        "ReceiveFileName": "@{body('Decode_AS2_message')?['AS2Message']?['FileName']}",
        "ReceiveProtocol": "AS2",
        "ReceiveProtocolDetails": "@{body('Decode_AS2_message')?['AS2Message']?['AS2From']} > @{body('Decode_AS2_message')?['AS2Message']?['AS2To']}",
        "ReceiveReference": "@{body('Decode_AS2_message')?['AS2Message']?['OriginalMessageId']}",
        "ReceiveTimeUtc": "@{utcNow()}"
      }
	},
	"authentication": {
-     "username": "Invictus",
-     "password": "@parameters('invictusPassword')"
-     "type": "Basic",
+     "identity": "[parameters('infra').managedIdentity.id]",
+     "audience": "[parameters('invictus').authentication.audience]",     
+     "type": "ManagedServiceIdentity"
	}
  },
  "runAfter": {},
- "metadata": {
-   "apiDefinitionUrl": "[parameters('invictus').framework.matrix.v1.definitionUrl]",
-   "swaggerSource": "custom"
- }
}