Azure APP Services - Logic Apps process Flat file transform to EDIFACT send to FTP

Logic apps is part of the new and awesome features Microsoft released in preview this spring. Focus on this type of functions are cloud integration! Both hybrid integrations that bring data to and from servers inside your network in a secure and easy way and also pure cloud integration.

Logic Apps is the application that is created in Azure as the workflowish application, I won’t explain more in this post but please read more here.

So the example I’ll take here is a classic one, I have a system that produces small and simple flat files on a local server and we want to bring that data in EDIFACT INVOIC format to a FTP server. Normally this would be solved via an integration platform like BizTalk but for this scenario we don’t have a BizTalk server that we can use so we try to solve this via Logic Apps instead.

Scenario:

1. Pick up a file on the local filesystem
2. Decode the flatfile and transform it to EDIFACT
3. Encode EDIFACT
4. Send to FTP server

Let’s jump right in into the portal, we need to start by setting up some connectors (File, FTP) and several API Apps from the marketplace will be used.

Preparations

SQL Databases

First off we need one SQL DB, you can use a previously created one but we need 2 empty instances, I did this in the “old” portal.

The 2 API apps are the following and in brackets you can se my database instance name.

1. BizTalk Trading Partner Management (TradingPartnerManagement)
2. BizTalk EDIFACT (EDIDatabase)

Copy the connection strings and change the text {your_password_here} to your password so you can use it later.

Service Bus:

Create a Service Bus namespace for the File Connector.

Schemas:

Download the EDIFACT XSD schema here

Mapper:

Create a BizTalk Service mapper in Visual Studio 2012

1. Create a Flatfile schema
2. Create the mapping from the flatfile schema to Edifact in Visual Studio.
   1. BizTalk Service mapper (generating trfm files). I don’t like this mapper since it have bad xslt support so I normaly just create a standard BizTalk project and do the mapping in a temporary project and later copy the xslt into the trfm mapper. J

Resulting in these 3 files for me (after downloading the EDIFACT schema)

 

Now when we are prepared let’s start creating the services

Creat and configure the API App’s instances.

In Web API section:

Create File Connector: We need a File Connector for picking up files on the onpremise server we need to provide the following information right away when we create the connector:

• Root Folder, make sure to write down or remember the value or prepare the server with the catalog structure since the value is impossible to find (if you do find where to read this value, please let me know how!)
• Service bus Connection String, take the apropiate SAS connection string from the earlier create service bus

After creating this we can install it on the server, see the section about installing File Connector for more info.

Create FTP Connector: We need a FTP Connector to be able to send messages to the FTP server. For this we need to provide the following information right away when we create the connector:

• **Server Address **(make sure to not end with a / or similar since they will automatically add :21 for the port then you get an error when trying to use the connector, se image bellow for how a file name is constructed in this case (notice the staring :21)
• User name, for the ftp server
• Password, for the user

Create BizTalk Flat File Encoder:

We need a BizTalk Flat File Encoder that will decode and encode of flatfile to/from XML. In our case it will be decode of flat file to XML

Installation is straight forward and you only need to apply name for the instance.

Configure BizTalk Flat File Encoder:

For the Flat File encoder to work we need to add one or more flatfile schemas. These schemas are the same that is used in BizTalk so you can easily reuse the once you have today. Go in to the instance we created (remeber the name of the instance) easiest way to find it is in the API apps list. (Browse -> API apps)

• Click Schemas
  1. Press Add Button
  2. Upload the Flat File Schema (must be a BizTalk flat file schema XSD file)

Create BizTalk Transform Service

We need a BizTalk Transform Service to be able to transform the xml instance of the flatfile to an xml instance of the EDIFACT schema. Creating this is quite straight forward just need to apply a name for the service.

Configure BizTalk Transform Service

After creation  we need to some configuration, we basicly need to add the maps. Pick up the app as with the others, when you have found it do the following:

• Click Maps
  1. Press Add button
  2. Upload the Map you prepared in the preparations

Create BizTalk Trading Partner Management

BizTalk Trading Partner Management is used to keep information about partners and setup agreements on AS2, EDIFACT and X12.

When creating we need to provide the connection string to the earlier created Database.

Configure BizTalk Trading Partner Management

When the Trading Partner Management is created we need to do some configurations. Mainly we need to create parties that are suppose to be used in the B2B flow and then connect them via agreements so we can say that partner A are exchanging messages with partner B.

First we need to create atleast 2 partners, go in to the instance (just like the others):

• Click Partners
• Press Add button and add at least 2 Partners (only name is mandatory)
  1. After the Partners are added we need to create Identities for them (you might want to reload the web API to make sure it will load properly) After that press one of the partners
  2. Press Profiles
  3. Press the Profile in the Profiles section
• Press Identities (see image below for a guidance)
• Enter the identity value and pick a Qualifier
  1. Press Save, repeat the steps from point 3 for the other partner

When the partners are created and we have added identities to them we can start creating agreements BUT to be able to create EDIFACT agreements we also need to provide the EDIFACT schemas that we want to use.

• Press Schemas
  1. Press add
  2. Upload the Schema (repeat for more shemas if needed)

Now we can create the agreement:

• Click Agreements
  1. Press Add button and enter a name
  2. Choose Protocol, in our case it’s EDIFACT
• Partner Settings (here is a click intense part) set all values according to your setup (all must be set)
  1. Enter receive settings I left all of this standard and just pointed out the INVOIC schema.
  2. Enter Send settings I left all of this standard and just pointed out the INVOIC schema and added Application Reference.
  3. Enter Batch settings I filled in the mandatory fields (name and counter =1 ) If this is skipped Azure will try to create the agreement but it will fail so just fil it in.

Note! After Save write down the Agreement ID, we will use that later on in the Logic App

Create BizTalk EDIFACT

The BizTalk EDIFACT instance will do the hard work of encode or decode your EDIFACT message to XML or vice versa. When creating the instance we need the following:

1. Database Connection string: Connection string to the earlier created Database for EDIFACT.
2. TPM Instance Name: The name of the earlier created BizTalk Trading Partner Management (from section 5)

There is no need to do some configuration since the EDIFACT instnace will use the configuration we did in the BizTalk Trading Partner Management instance when handling agreements and so on.

 

Now we are all set and the preparations are done, let’s start building the Logic Apps app.

Building the app looks easy and are smooth but there are some tricky parts that you will encounter since we will have some looping lists in this way of building the Logic App. (Building it this way so it’s easier to understand and test, quite bad though that if I need to change this later I have some serious remodeling to do, it’s easier to create a new flow.)

As I created it I focused on a flow that would be easy to demonstrate, so I added the fileconnector in a read mode of a folder, instead of the event mode that also is available. (With the read mode I can trigger it at any given time, perfect for demo or when we have need of manual runs).

So let’s start!

First of we pick up the files from the server.

1. Add a Recurrence, I set this to standard values (once per hour, depending on the Tier choice this might be the lowest value that you can set).
2. Add the File Connector and use the function List Files, since I will pick up the files from the root folder (that we set when we created the Connector, in my case:  C:\temp\NewFileConnector) I’ll leave the Folder Path blank.
3. Add a second File Connector to actually pick up the files that the List Files function located. Use function Get File, and since the List Files result is a collection we need to add “Repeat over list” and pick the list from List Files.
   1. Repeat over the items returned from the first File Connector.
   2. Set File Path to: @repeatItem().FilePath (which declares that we should take the FilePath result from the repeating item.  (need to be set manually since the automatic help will give you only use the first() function that will give you the information from the first file.
4. (Optinal and not included in my sample) Add a third File Connector with function Delete File to delete the file from the folder (to prevent picking it up serveral times)
   1. Repeat over the list from File Connector Get Files
   2. File Path, should be the File Path from the repeating item

Now we have this setup and we will start picking up files, eaither on the intervall or at the time we manually start the logic app.

Next section will be adding the BizTalk Flat File Encoder, transformation and the BizTalk EDIFACT component.

• Add the BizTalk Flat File Encoder, function Flat File to XML
  1. Repeat over the File controller Get File.
  2. Flat File element should be the content from the Get File operation and since it’s a list we will need to fix this manually.
  3. Schema name must be set, in my case it’s TestInvoice
  4. Root name must be set in my case it’s Invoice
  5. Add the BizTalk Transform Service, now we will transform the xml version of the flatfile to the xml version of the EDIFACT.
     1. Once again we need to repeat over a list, in this case the result list from the BizTalk Flat File Encoder.
     2. Input XML should be the output from the flatfile encoder.
        1. Add the BizTalk Edifact to transform the xml version of the EDIFACT to an actual EDIFACT.
  6. Repeat over the results from the BizTalk Transformation Service
  7. Content should be the xml result from the transformation.
  8. Agreement ID is the EDIFACT agreement ID that is created in the TradingPartnerManagement API App, we wrote this down earlier. (you can also collect this at any given time in the Trading Partner Management instance) 3. To send this to the FTP add the FTP Connector
  9. Repeat over the result from the BizTalk EDIFACT instance
  10. Content should be the Payload that is returned as a result from the EDIFACT instance.
  11. File Path, make sure to find something unique, I used the tracking ID to make the file have a unique name. And also the built in function @concat, that will concatenate several strings. (make sure to not use the @ sign inside the @concat function since that will give you an error)

In my setup the files will not be deleted so I don’t need to redrop files over and over again. (If we want that behavior just add another File Connector at position 4 (marked as optinal in the setup) or change the whole setup so we use the trigger function in the File Connector that will keep track of new files and pick them up and afterwards delete the file in one step. The dissadvantage with that is when you want to demo since it’s not possible to manually trigger that flow via the start flow buttton.

Result will be, that we have a full functional flow that will pick up the flatfile and do all the handling that is needed to eventually end up as an EDIFACT file on the FTP server. Here is how the full flow look like for me:

Key Take Aways

Crashes:

This is a preview so don’t be afraid of crashes, I also learned that a lot of the time it was just the async GUI that had the problems especially if I used the Free tier. Then I quickly filled my quote and in combination with heavy traffic or high demand I was unable to work with it. But when I switched to Basic Tier almost all problems just disappeared =) Updating the map

Strangely I couldn’t update the map, I had to delete it and upload it again.

Installing the File Connector:

To install the File Connector, find it in the portal (easiest way is to go via the Browse) and pick API Apps select the file connector.

You will notice that when loading the settings it will try to check if the connector is installed, click the link.

Next is to download the installer, click “Download and Configure” see image bellow

After downloading, copy and install the application on the prefered server. When you get promted for the connection string as bellow you should the Primary Configuration String (yellow marked in the image above).

 

Tips when using the File Connector

• File name cannot contain spaces, that will cause internal server errors in the File Connector (not sure why)

Finding the files and Installed content on your server:

After the micro service is installed it will be found in IIS

When browsing the file system we will find a folder containing some files that represents a web api:

Testing the installed API App locally with swagger on your server

Troubelshoothing the flow:

So when you get this red error and you have no idea what went wrong we need to start troubleshooting. In the editor it looks and feel so smooth to do things but there is not as easy finding the errors, specially not the first times. For us who has been in the business for some time we are use to troubleshoot and understand xml notations and layouts so this can be a little bit new since Logic App is building it’s settings, configuration and input/output on JSON notation, meaing no xpaths but a .(dot) notation instead.

First of all in an JSON object we use the [object}.{attribute} to access something so when looking at this example we will need the following notation in the field to get the value.

Check the error output messages, example bellow on error in EDIFACT service, reason is found under output.body.Exception

Other tips when working in the portal.

Watch out for portal related issues also, like when editing Partners in Trading Partner Management, make sure to wait for the green save signal before proceeding to fast to the next step. I found myself having trouble when I jumped to fast around in the portal.

Posted in: •Integration  | Tagged: •AzureAPIApps  •API Apps  •EDIFACT  •FileConnector  •FlatFile  •FTP  •Logic Apps  •Microsoft Azure