Biztalk Recipe – Content Based Routing Example

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.

A DOS Command To Map A Network Drive

Just a quick post with DOS commands to map a network drive connecting as a specific user and logging the results.

echo Start %date% %time% >> D:\logs\drivemap.txt
net use z: /delete /yes >> D:\logs\drivemap.txt
echo Mid %time% >> D:\logs\drivemap.txt
net use z: \\10.10.10.10\INVOICEFILES /user:USERNAME PASSWORD /persistent:yes >> D:\logs\drivemap.txt
dir z: >> D:\logs\drivemap.txt
echo End %time% >> D:\logs\drivemap.txt

This is something we did for a project a while back and want to keep a note for future.

Biztalk – Installing Schema Editor Extensions

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

The steps to create a schema from an existing XML file are

  1. In Solution Explorer, right-click a BizTalk project, point to Add, and then click Add Generated Items…
  2. In the Add Generated Items dialog box, in the Templates section, click Generate Schemas, and then click Add.
  3. In the Generate Schemas dialog box, in the Document type drop-down list, select Well-Formed XML.
  4. Browse to select an Input file and click OK

When following the above steps the Document type drop down on the Generate Schemas dialog may display ‘Well-Formed XML (Not loaded)’.
biztalk-generate-schemas-dialog
A message similar to below will be displayed (the path will be different depending on the version of Biztalk installed)
biztalk-schema-generator
When running the InstallWFX.vbs file (or InstallDTD.vbs file) a dos prompt may flash up and although it appears to have worked the same message will be displayed as before.
In fact from my own experience, in the past I have tried different combinations of

  • Running the file by double-clicking from Windows Explorer.
  • Running the file as administrator from Windows Explorer.
  • Opening a Command Prompt and running the file.
  • Opening a Command Prompt as administrator and running the file.
  • Opening a Developer Command Prompt for VS2012 and running the file.
  • Opening a Developer Command Prompt for VS2012 as administrator and running the file.
  • Restarting Visual Studio after each attempt.

Hopefully one of these options work, if not then some people have reported to solve the situation by manually coying the Microsoft.BizTalk.WFXToXSDGenerator.dll and/or Microsoft.BizTalk.DTDToXSDGenerator.dll files to the extensions folder. i.e.
from
C:\Program Files (x86)\Microsoft BizTalk Server 2013\SDK\Utilities\Schema Generator
to
C:\Program Files (x86)\Microsoft BizTalk Server 2013\Developer Tools\Schema Editor Extensions
again, paths may differ depending on the version of Biztalk installed.

If copying the files works for you it may be worth taking this approach first every time.

Compare Files In Folders And Copy If File Size Is Different

The Problem
I have a remote folder containing nearly 8000 images and a local folder containing just over 300 images.
Rather than performing a blank copy of files from the local to remote folder I needed a way to check if the file exists in the remote folder and take a different action:

  • If a file exists in the local folder but not the remote folder, the file should be copied to the remote folder.
  • If a file of the same name does already exist in the remote folder I only wanted to copy the file if the new file in the local folder was smaller in size.

I started on Google searching for some way of conditionally copy files but most of the results described features of an application that would need to be installed.
Although these applications are free, installing an application is not an option as I do not have administrator level permissions, corporate security and all that.
My next option was to find some clever DOS commands and so I returned to Google.

Possible Solution

A possible solution was found in this post on stackoverflow by (and full credit to) mojo. Below is the solution mojo posted.

@ECHO OFF
SETLOCAL ENABLEDELAYEDEXPANSION
SET REMOTE_DIR="REMOTE_FOLDER_PATH"
SET LOCAL_DIR="LOCAL_FOLDER_PATH"

FOR /F %%f IN (files_to_compare.txt) DO (
    SET "REMOTE_FILE=%REMOTE_DIR%\%%~nxf"
    SET "LOCAL_FILE=%LOCAL_DIR%\%%~nxf"
    FOR %%l IN (!LOCAL_FILE!) DO (
        @ECHO [LOCAL] %%~nxl: %%~zl
        FOR %%r IN (!REMOTE_FILE!) DO (
            @ECHO [REMOTE] %%~nxr: %%~zr
            IF %%~zr GTR %%~zl (
                @ECHO %%~nxr: Remote file IS larger [%%~zr] ^&gt; [%%~zl]
                COPY /Y "%%~r" "%%~l"
            ) ELSE (
                @ECHO %%~nxr: Remote file is not larger [%%~zr] ^&lt;= [%%~zl]
            )
        )
    )
)

Working Version

To get a working solution

1.) Copy the above code (provided by mojo) to a file named files.bat
2.) Specify the remote and local folder paths labelled REMOTE_FOLDER_PATH and REMOTE_FOLDER_PATH
3.) Create a text file called files_to_compare.txt containing the file names in the local folder, one filename per line.

Creating the list of local files in the files_to_compare.txt file can be done by changing to the local folder in a dos command window (using the cd command) and running the following. The /b switch suppresses the heading or summary information from being included. The > (greater than) redirects the output of the command to a destination specified in this case as a file rather than directing the output to the command window. In my case the text file was being created one level above the local folder so I could use a relative path to specify the desired location of the text file.

dir /b > ..\files_to_compare.txt

4.) Run the batch file and reveiw the result

Run the batch file and direct the ouptut to a text file.

files &amp;amp;amp;amp;amp;amp;amp;amp;gt; results.txt

The results.txt file now contains the results of the comparison.

If you want to perform a test run without copying any files add an @ECHO instruction before the Copy command on line 16.

@ECHO COPY /Y "%%~r" "%%~l"

Reviewing the results file will highlight which files will be copied.

Extending the Solution
What the script is actually doing is this:

For each file in the files_to_compare file
Look for the file in the local folder and get its details
Look for the file in the remote folder and get its details
If the size of the remote folder is greater than the size of the local folder
Copy the file from the local folder to the remote folder
Otherwise
Do nothing because the file in the remote folder is smaller

The script is currently set to check of the size of the file in the remote folder is greater than the file in the local folder. This can be seen on line 14

IF %%~zr GTR %%~zl (

It may be that you do not want the comparison to be greater than, here are some alternatives
EQU – equal
NEQ – not equal
LSS – less than
LEQ – less than or equal
GTR – greater than
GEQ – greater than or equal

On the whole this solution worked for what I needed on this occasion and I can see will be useful in the future.