Schemas

Date Formatting and Calling C# Script From XSLT in BizTalk 2013 Map

DaveLeave a comment

I needed to set a field value to a formatted date string in a map. As we are using BizTalk 2013R1 we are restricted to XSLT1.0 which makes date formatting more difficult (XSLT2.0 has better support for dates). However this got me thinking of the different ways this could be achieved. For demonstartion purposes lets say I have the below schema

biztalk-date-functoid
the last three fields are the fields of interest and are set up with the data types as in this table

Field Data Type
ApprovedOn xs:dateTime
CreatedDate xs:date
CreatedTime xs:time

Setting the ApprovedOn field to the current datetime value is quite straight forward with a C# scripting functoid.

biztalk-date-functoid-linked
Setting the CreatedDate and CreatedTime fields can be done in a similar way and just requires a C# scripting functoid for each field that returns the date in the relevant format.

The CreatedDate C# scripting functoid

public string CurrentDate()
{
    return DateTime.Now.ToString("dd-MM-yyyy");
}

The CreatedTime C# scripting functoid

public string CurrentTime()
{
    return DateTime.Now.ToString("HH:mm:ss");
}

This is the result of testing the map with a valid input file.

<Employee>
  <ForeName>Percy</ForeName> 
  <ApprovedOn>29/09/2016 11:59:43</ApprovedOn> 
  <CreatedDate>29-09-2016</CreatedDate> 
  <CreatedTime>11:59:43</CreatedTime> 
</Employee>

Moving on to the orignal problem I was trying to solve
The destination schema contains an element which itself contains three child elements

biztalk-employeeoutput-schema

defined with the following data types

Field Data Type
Date xs:date
Time xs:time
Type xs:string

In the real life scenario the Type field is calculated from a number of fields in the source schema. To keep this example focused I have hard coded the Type field using a String Concatenate functoid. The output of the Concatenate is an input parameter to a scripting functoid that builds the Action destination element using an Xslt template. The value for the Type field could have been hardcoded in the scripting functoid but linking this way demonstrates parameter handling in an Xslt template.

biztalk-map-complete

So here we have a scripting funcoid (with the exclamation warning) containing inline C# code that consists of a singel FormatCurrentDateTime method.  This method has a single string parameter which will format the returned string.

public string FormatCurrentDateTime(string format)
{
    return DateTime.Now.ToString(format);
}

BizTalk adds the C# script in a block at the bottom of the xslt when the map is compiled. This can be seen by compiling the assembly containing maps, and using the Show all Files command in Visual Studio Solution Explorer to look at the corresponding .btm.cs file to see what has been added.
There is also a scripting functoid containing an xsl template. This template accepts a single parameter for the Type element. There are also two variables initialised by calling the FormatCurrentDateTime method passing in a string defining the format of the DATETIME returned.  Note the use of userCSharp: prefix on the method call.

<xsl:template name="mapActionTemplate">
    <xsl:param name="type" />
    <xsl:variable name="date" select="userCSharp:FormatCurrentDateTime('yyyy-MM-dd')"/>
    <xsl:variable name="time" select="userCSharp:FormatCurrentDateTime('HH:mm:ss')"/>
        <xsl:element name="Action">
            <xsl:attribute name="Type">
                <xsl:value-of select="$type" />
            </xsl:attribute>
            <Date>
                <xsl:value-of select="$date" />
            </Date>
            <Time>
                <xsl:value-of select="$time" />
            </Time>
        </xsl:element>
</xsl:template>

For additional information on maps see this series of articles on Bizbert.

Credit to the original article:
Useful Cheat Code in Mapping (Calling C# code from XSLT) « BizTalk Server Tutorial

Biztalk Recipe – Content Based Routing Example

DaveLeave a comment

This post is more about the technique rather than the scenario, so the solution is somewhat over engineered for what the scenario requires.
I generally create a separate Biztalk Server Project for Maps, Schemas, Orchestrations and Pipelines. Each project may then contain folders to group related content.

Environment:
Windows Server 2008 R2 Enterprise
Biztalk 2013 R1
Visual Studio 2012
MSSQL Server 2008 R2

Example Scenario
The HR department has moved the employee records to a new all singing all dancing record system as part of the HR phase 1 project.  The payroll system is still in the legacy system and is being integrated with the new HR system in phase 2.  Until then data relating to new employees must be exported from the new HR system and imported into the legacy payroll system.  For this to work efficiently an automated process is preferred.  So far the new HR system is configured to automatically create a file containing basic employee details each time a new employee is created.
Biztalk will form part of the process by moving the employee files to a specific folder, which folder the file is moved to will be controlled by a value contained in the XML file itself (Content Based Routing).

The XML file in this example is very simple. The value of the Department will determine which folder Biztalk routes the file to.
For the purpose of this example there are two files, one called Employee_HR.xml

<Employee>
    <ForeName>Percy</ForeName>
    <Surname>Philips</Surname>
    <DOB>1980-12-05</DOB>
    <Department>HR</Department>
</Employee>

and one called Employee_IT.xml

<Employee>
	<ForeName>John</ForeName>
	<Surname>Jones</Surname>
	<DOB>1980-12-05</DOB>
	<Department>IT</Department>
</Employee>

The new HR system places the files in the location E:\Test\Input\EmployeeDefinitions.
If you are following along copy the two XML definitions above in to files and save them on your computer so you can locate them later.

The requirements state that (we will look at this in greater detail later)

  • If the employee department is HR the file should be placed in the E:\Test\Output\Employee_HR folder.
  • If the employee department is IT the fiel should be placed in the E:\Test\Output\Employee_IT folder.

Solution Part 1 – Visual Studio
The first we need is the schema of the Employee Definition XML file.

  1. Open Visual Studio and open/create a project to hold the schemas we will be creating.
    If a new project was created for this example make sure the the Application Name on the Deployment tab of the project properties is set.
  2. Right-click the project in Solution Explorer and select Add then Add Generated Items… (If you prefer to use the main menu, on the Project menu, click Add Generated Items…)
  3. In the Add Generated Items dialog box, click Generate Schemas in the Templates section then click the Add button.
  4. In the Generate Schemas dialog select Well-Formed XML for the Document Type and for the Input File browse to and select one of the employee definition files you saved earlier.  It does not matter which file as both have the same schema.If you see Well-Formed XML (Not Loaded) in the drop-down list, Microsoft suggests you continue and select the appropriate document type anyway, and you will be guided through the process of installing the missing DLL. Then repeat these steps.

    For additional help see the post on Biztalk-Installing Schema Editor Extensions
  5. Click OK once a file is selected.

If the schema generated successfully there will now be a schema file with a name similar to the file selected (different extension .xsd).

biztalk-imported-employee-schema

For parts of Biztalk to be able to read the value of the Department we need to set the department as a promoted property.

  1. Expand the Schema tree so the full schema is visible.
  2. Right-click the Department and in the context menu select Promote then Quick Promotion. Biztalk will inform that a new file is being added to the project and that the Employee schema has changed.
  3. You should see a new schema named something like PropertySchema.xsd.

biztalk-promoted-department-property
As Visual Studio has created the schema file it will contain the property that was just promoted and may even contain an default Property1 property which can be deleted. This is all we need to do in Visual Studio. The project can now be built and deployed.

Solution Part 2 – Biztalk Server Administration Console
Looking back at the rules specifed above Biztalk needs to be able to receive a file from a location, process the file and save the file to a location.

Receiving a file from a location – Set up a Receive Port/Location

  1. Open Biztalk Server Administration
  2. Expand the console tree through the following nodes until you can see the application you want to create a receive port. In my case this is the Learning application.
    • BizTalk Server Administration
    • BizTalk Group
    • Applications
  3. Right-click Receive Ports, point to New, and then click One-way Receive Port.
  4. Give the receive port a Name.
    biztalk-receive-port
  5. In the left pane, click Receive Locations
  6. In the Receive Locations panel click New
  7. In the Receive Location Properties window specify a Name of the Receive location.
  8. Select XMLReceive from the Receive pipeline drop down.
    biztalk-receive-location
  9. In the Transport group, select FILE from the Type drop down and click Configure
  10. In the FILE Transport Properties dialog, on the General tab,  browse to the folder where the file will be picked up from.  In this scenario it is the folder the new HR system is configured to automatically create a file containing basic employee details each time a new employee is created.
    biztalk-receive-location-configure
  11. Click OK on each dialog to return to the Receive Ports list.

Sending a file to a location – Set up a Send Port

Setting up a Send port is very much like setting up a Receive Location.  First we set up where BizTalk will place the files for employees in the HR department.  For HR employees the folder will be E:\Test\Output\HR and for IT employees it will be E:\Test\Output\IT.  For you these location will probably be different.

  1. Right-click Send Ports, point to New, and then click Static One-way Send Port.
    biztalk-send port-hr
  2. In the Transport group, select FILE from the Type drop down and click Configure to open  the FILE Transport Properties dialog.
  3. In the FILE Transport Properties dialog, on the General tab,  browse to the Destination folder where the files for HR department will be placed, in my case E:\Test\Output\HR.
  4. Click OK.
  5. Select Filters on the left navigation.
  6. Create 2 filters
    biztalk-send-port-hr-filters

    • Property: BTS.ReceivePortName
      Operator: ==
      Value: ReceiveEmployeeDefinition
    • Property: Learning.Schemas.Employees.Department
      Operator: ==
      Value: HR
  7. Click OK.

To set up where BizTalk will place the files for employees in the IT department repeat the above steps.  I set the Destination folder on the FILE Transport Properties dialog to be E:\Test\Output\IT.  I set the filters for picking up the IT files as

  • Property: BTS.ReceivePortName
    Operator: ==
    Value: ReceiveEmployeeDefinition
  • Property: Learning.Schemas.Employees.Department
    Operator: ==
    Value: IT

That should be it.  Ensure the Receive Locations are Enabled and the Send Ports are started.

When a file is created in EmpoyeeDefinitions folder by the HR system Biztalk should pick it up and place a copy in either the HR or IT folder depending on the value of the department.

Things To Note
Are you familiar with the saying “There’s more than one way to skin a cat”, gross I know. Putting cats to one side there is certainly more than one way to create a schema or a promoted property.