Creating a New BizTalk Map
In this chapter, we will create a map between two BizTalk specifications, specScoreReport.xml, representing an XML input document, and specGradeReport. xml, representing an XML output document. SpecScoreReport contains a record for each test given during a single grading period. The specGradeReport file contains a single record that displays the student's cumulative grade as a result of those scores.
In the next several sections, we will walk through the steps required to create, test, and compile a map that transforms and accumulates the student's score into a semester CSV grade file.
Note
The BizTalk specifications and maps for this chapter can be downloaded from the publisher's Web site.
Choosing Source and Destination Specifications
To create a new map using the BizTalk Mapper, choose Start, Programs, Microsoft BizTalk Server 2002, BizTalk Mapper. Then choose File, New from the menu. Because a BizTalk map needs to reference both a source and a destination specification, a dialog appears immediately asking you to specify them (see Figure 5.3). You can choose specifications from a local file directory (on your machine or accessible by file share), a WebDAV file (Web Distributed Authoring and Versioning) location, or a template file provided by Microsoft. When you configure BizTalk Messaging, the file specifications are stored in a WebDAV repository, so in production situations you will usually create maps from files located in a WebDAV repository.
Figure 5.3. Choosing file specifications for a new map.
Examining Properties of Specification Records and Fields
Figure 5.4 shows source and destination specifications to map a document containing student scores for midterm exams, the final exam, homework, and so on, to a document containing the students' final grades expressed as a percentage and also as a letter grade. Note if we select the Properties tab in the bottom pane, we can select a record or field separately on both the left and right specification panes to examine (but not to modify) the properties of the selected record or field. In the example, note that the source ScoreItem record can appear zero or more times, whereas in the destination the StudentInfo record must appear exactly once.
Figure 5.4. Record properties in BizTalk Mapper.
Linking Source Fields to Destination Fields
To copy the course ID from the source to the destination, simply click the CourseID field in the left pane and (holding down the mouse button) drag to CourseID in the right pane. When you release the mouse button, a link is drawn from the source field to the destination field. In the same manner, we link the source CourseTitle and StudentID fields to the corresponding fields in the destination specification (see Figure 5.5).
Figure 5.5. Linking fields in BizTalk Mapper.
Using Functoids to Transform Data
The student name cannot simply be copied from source to destination because the source uses separate first and last name fields, whereas the destination uses a single field. To combine the data from two separate fields into one field, we use a functoid, which simply refers to the embodiment of a mathematical, string, or other function (multiple input values, single output value) as a programming object.
To show the Functoid Palette, select View, Functoid Palette from the menu or click the yellow-background Palette icon on the toolbar. The String tab is displayed when the Palette is opened. Move the cursor arrow over the icons, reading the ToolTip text until you find the Concatenate functoid. Click and drag it to the mapping grid, the center pane between the two specification panes (see Figure 5.6).
Figure 5.6. Adding a functoid to the mapping grid.
To transform the two separate name fields into a combined field, drag the StudentLastName field to the Concatenate functoid; then do the same for the StudentFirstName field. To copy the functoid result to the destination field, drag the functoid icon to the StudentName field. This result is not yet satisfactory because the last name and first name will be combined into a single string with no separation. To add punctuation between the names, double-click the functoid icon (or right-click and choose Properties), click the Insert New Parameter toolbar button, and enter a comma and a space (see Figure 5.7).
Figure 5.7. Input parameters of a functoid.
Updating the Source or Destination Specification
When you choose the source and destination specifications for a new map, those specifications are copied into the new map definition. Later changes to the specifications are not automatically incorporated into the map. To update either specification in the map, right-click the source (left) or destination (right) pane in BizTalk Mapper and choose Replace Specification. If our example Score Report specification were changed to place the StudentID field before the name fields, BizTalk Mapper would attempt to retain as many existing links as possible by matching field names from the old specification to the updated specification, as in Figure 5.8—compare to Figure 5.6 to see that the source fields have been reordered, but the links have been retained. The use of field name matching to maintain existing map links when a specification changes is a good reason to make record and field names easily distinguishable within the specification.
Figure 5.8. Updating the source specification.
Adding a New Mapping Grid Page
A new feature of BizTalk Server 2002 is the capability to separate the map into several pages, accessible by tabs at the bottom of the mapping grid (see Figure 5.9). This is especially useful when complex source and destination specifications make it difficult to fit the entire map onto the grid pane so that it is both readable and all visible at once. Whether the map is simple or complex, it makes good sense to use a separate page for each logical coherent subunit of the mapping. In our scores to grades example, we used the first page to copy (or transform) the textual identifying data that appears in exactly-once records. Now we will use the second page to transform the numerical grading information that appears in zero-or-more-occurrences records.
Figure 5.9. Adding a new mapping grid page.
In Figure 5.10, the ScorePercent field in the ScoreItem records represents the percentage score (0 percent to 100 percent) achieved on individual components of a student's course grade: midterm exams, homework, final exams, and so on. The ScoreWeight field represents the weight given to each component (for example, 0.20 for a midterm, 0.4 for the final, and so on for a total of 1.0). Each ScorePercent should be multiplied by its ScoreWeight, and we have linked both fields to a Multiplication functoid to accomplish that in the mapping.
Figure 5.10. Cascading functoids and aggregating results.
The next step illustrates that functoids cannot only link a source field to a destination field, but also functoids can be cascaded to perform a multistep transformation. In this case, we need to add up all the score x weight products to compute the student's total score for the course. To do that, we link the output of the Multiplication functoid to the Cumulative Sum functoid, which returns the sum of the input value iterated over all record instances that occur in each parent record (or the whole document, for top-level records). We can then link the output of the Cumulative Sum functoid (the weighted sum of all component scores) to the destination field GradePercent.
A design feature of BizTalk Mapper that does not become apparent until you cascade functoids is that all data flows are strictly left to right on the mapping grid. The Mapper enforces the direction of flow for functoids already linked by displaying red hatching when a cascaded functoid is selected for moving (see Figure 5.11). You cannot move the selected Multiplication functoid into the red-hatched area of the map grid because that would reverse the direction of data flow. When two functoids are not yet linked, it is possible to draw a link from the functoid more to the right side of the grid to the functoid more to the left. However, the data flow will still be from the left functoid to the right one: The direction in which you click and drag to draw the link does not matter. To avoid confusion, it might be best always to draw links from left to right as a mnemonic cue to the direction the data will be propagated during map execution.
Figure 5.11. Enforcing left-to-right data flow.
The final item we need to supply to the destination is the GradeLetter field. There is no (and to define how many and built-in functoid to compute an academic letter grade from a percentage, so we must make one of our own. An easy way to do that is with the Scripting functoid, which allows you to write a simple (or complex!) function in VBScript or (new for BizTalk 2002) in JScript. As Figure 5.12 shows, the Scripting functoid is on the Advanced tab of the Functoid Palette. You can add a Scripting functoid to the map just like any other and link it to its input and output fields (or functoids). To define the script to be executed functoid will expect), double-click the Scripting functoid to open the Properties dialog, click the Script tab, and choose the language to use (VBScript or JScript). A template function with a generic name will be added automatically to the script window, but because all functions in the map are in the same namespace, you should name your script what type of parameters the function as specifically as possible.
Figure 5.12. A custom Scripting functoid.
Note that a Scripting functoid can have as many input parameters as you want, but the script function must return a single, string-formatted result. Finally, any variables you declare outside the script function itself will become global variables that hold their values for the duration of the map execution. That can be useful for creating your own Cumulative functoids; see the Properties, Script tab for the Cumulative Average functoid, which can be found by selecting the Cumulative tab, for a good example of how to use global variables.
For custom functoids too complex for scripting, or where a specialized action is not available and scripting is required, you can create and use functoids as COM+ components. A complete Visual Basic sample of a COM+ functoid is provided in Program FilesMicrosoft BizTalk ServerSDKMessaging SamplesSampleFunctoid. To create a COM+ functoid using Visual C++ or C#.NET, go to BizTalk Server Documentation, Search tab, iFunctoid for full details on the component interface and special registration requirements.
Replacing the Scripting Functoid with a Database Lookup Functoid
In the previous section, we used a scripting functoid to convert the cumulative percentage into a letter grade for the GradeLetter element in the GradeReport schema. In many cases, you will need to perform a database lookup to extract the values for certain fields as opposed to script written in the functoid. To demonstrate this, we are going to modify the mapScoresGrades.xml map by deleting the Scripting functoid and adding two new functoids, the Database Lookup functoid and the Value Extractor functoid. The Database Lookup functoid is going to query a simple SQL Server database named Grades that contains a single table, Grades, consisting of two fields GradeLetter and Percentage.
Note
The source code to create the SQL Server table and add the required records as well as the map file are downloadable from the publisher's Web site.
To accomplish this, select the Page 2 tab in the grid and delete the Scripting functoid. Then, open the Functoid Palette, navigate to the Database tab, and drag the Database Lookup and Value Extractor functoids onto the grid. Next, connect the Cumulative Sum functoid to the Database Lookup functoid and the Database Lookup functoid to the Value Extractor functoid. Finally, connect the Value Extractor functoid to the GradeLetter field in the GradeReport schema as displayed in Figure 5.13.
Figure 5.13. Adding the Database Lookup functoid.
The Database Lookup functoid performs a database lookup to find the appropriate record from the appropriate table. The output of the Database Lookup functoid can be connected only to the Value Extractor functoid. The Value Extractor functoid extracts the appropriate column name from the lookup record.
The Database Lookup functoid requires four input parameters, in the following order:
The lookup value
The database connection string
The table name
The column name for the lookup value
The Value Extractor functoid requires two input parameters, in this order:
The output from the Database Lookup functoid
The column name to extract from the results
The Database Lookup and Value Extractor functoid parameters are displayed in Table 5.1.
| Functoid | Parameter Name | Value |
|---|---|---|
| Database Lookup | Lookup value | Link from Cumulative Sum functoid |
| Database Lookup | Database Connection String | Provider=SQLOLEDB;server=(local);database=Grades;uid=sa; |
| Database Lookup | Table Name | Grades |
| Database Lookup | Column Name for the Lookup Value | Percentage |
| Value Extractor | Link to database lookup functoid | Link from Database Lookup functoid |
| Value Extractor | The column name | GradeLetter |
Figure 5.14 displays the Database Lookup functoid properties.
Figure 5.14. Database Lookup functoid properties.
Figure 5.15 displays the Value Extractor functoid properties.
Figure 5.15. Value Extractor functoid properties.
We are now ready to test the map. Select Tools, Test Map, Generated XML to XML from the menu. Remember that the ScorePercent test value is set to 91. The output from the test should appear as displayed in Figure 5.16.
Figure 5.16. Output from map test with Database functoids.
You also have the ability to test the Database Lookup functoid using the Error Return functoid. The Error Return functoid outputs a string containing error information if there are errors (such as connection failures) at runtime when connected to the Database Lookup functoid. You can use the Error Return functoid to pass the error string to a designated field in the output document specification.
For example, if you need to connect to a named instance of SQL Server (syntax: SERVERNAMEINSTANCENAME), you will not be able to use (local) as the name of the SQL Server in the database connection string. If you forgot that and had trouble connecting to the database, the Error Return functoid could be connected (temporarily) to any string field in the output document with a large maximum length (or no maximum length). Testing the map would quickly give you the information you needed to correct the problem.