Sunday, March 3, 2013

String Functoids

Posted by Ravi at 5:55 AM – 0 comments

The string functoids perform string manipulation on either an incoming value or a set of values. They all output a string, which can be used as the input for another functoid or sent directly to the destination schema. Table 3.5 lists the string functoids, their usage, and parameters.

Source: Microsoft® BizTalk® Server 2010 Unleashed

[ Read More ]

Saturday, March 2, 2013

Table Looping Functoid:

Posted by Ravi at 11:09 PM – 0 comments

Table Looping Functoid:

Use the Table Looping functoid , in conjunction with one or more Table Extractor functoids, to create a repeating structure in an output instance message by using constant values, values from an instance message, and values that are output from other functoids. The Table Looping functoid can also be used to add records to the output structure when there is no input record

Input:

Parameter 1: A link from a repeating node in the source schema. The number of instances of this structure that occur in a particular input instance message defines the number of times that the associated table looping grid is processed.
Parameter 2: A constant input parameter that defines the number of columns in the associated table looping grid.
Parameters 3 – 100: A link from a node in the source schema or from another functoid, such as a Value Extractor functoid, or a constant input parameter. The relative order of parameters 3 – 100 is unimportant.

Output:

Output 1: A link to a repeating node in the destination schema, identifying the location of the repeating structure in output instance messages that is constructed by using values configured in the associated table looping grid.
Outputs 2 – N: One or more links to the associated Table Extractor functoids, one per such functoid.

Notes:

You must use this functoid in conjunction with one or more Table Extractor functoids.

This functoid accepts multiple inputs, such as links from the source schema, constants, and the output of other functoids. You use these inputs to configure the table looping grid from which the associated Table Extractor functoids extract the data used to build the relevant portion of the output instance message You configure the table looping grid by using the Configure Table Looping Functoid Dialog Box, Table Looping Grid Tab. You access this dialog box by using the Table Looping Grid (Functoid Property). The Table Looping Grid is a property of Table Looping functoids.

Every cell in the table looping grid is a drop-down list that is populated by input parameters 3 through 100. How the entries in the drop-down list are represented depends on the type of the corresponding input parameter and, where applicable, on whether the Label property of the link is assigned a value. There are three possibilities:
1. Constant input parameters are displayed as their values.

2. Input parameters that are links from nodes in the source schema and for which no Label link property value is supplied are displayed by using the value of their Link Source functoid property.

3. Input parameters that are links from another functoid and for which no Label link property value is supplied are displayed by using the value of their (Name) property.

In the case of 2 and 3 above, and especially in the case of 2, where the input parameters are links from nodes in the source schema, the effort involved in providing descriptive values for the Label property of the relevant link is generally well spent when the time comes to configure the table looping grid.
The Table Looping Configuration dialog box also has a check box with the label Gated. When you select this check box, you are specifying that the value of the data specified for column 1 controls whether any processing is performed for that row. When this is output from a logical functoid, if the value specified in column 1 evaluates to False, the associated row of the grid is skipped (meaning that the associated Table Extractor functoids are not invoked for that row). When column 1 is a field, then the functoid treats the presence of data as True; it treats the absence of data as False.

Example:

Source Document:

- <ns0:Root xmlns:ns0="http://BTSMar13.TableLoopingSource">
<LoanNumber>1000</LoanNumber>
<Amount>10000</Amount>
<Barrower1FName>Ravi</Barrower1FName>
<Barrower1LName>M</Barrower1LName>
<Barrower1SSN>006</Barrower1SSN>
<Barrower2FName>Sand</Barrower2FName>
<Barrower2LName>M</Barrower2LName>
<Barrower2SSN>007</Barrower2SSN>
</ns0:Root>

Destination Document:

- <ns0:Loan xmlns:ns0="http://BTSMar13.TableLoppingDest">
<LoanNumber>1000</LoanNumber>
<Amount>10000</Amount>
- <Barrowers>
- <Barrower>
<LName>M</LName>
<FName>Ravi</FName>
<SSN>006</SSN>
</Barrower>
- <Barrower>
<LName>M</LName>
<FName>Sand</FName>
<SSN>007</SSN>
</Barrower>
</Barrowers>
</ns0:Loan>

List 1:

Notes: Please note that looping functoid is added to barrower.

List 2:

Notes:
Value 2 is no of repeating records and value is 3 no of columns.

List 3:

Notes:
Please select the required row and column combinations

List 4:

Notes:
Value 2 indicates that column 2 value will be selected and moved to the corresponding field on the destination schema.

[ Read More ]

Record Count Functoid:

Posted by Ravi at 9:49 PM – 0 comments

Record Count Functoid:

Use the Record Count functoid to generate a count of the number of times a particular repeating structure occurs in an input instance message.

Input:

Parameter 1: A link from a node in the source schema. For this functoid to do something interesting, the structure in an input instance message that corresponds to this node must be repeating. Otherwise, the output is always one (1).

Output:

Output 1: A number that corresponds to the number of occurrences of the repeating structure in an input instance message that corresponds to the node specified by parameter 1.

Note:

You can also use the Record Count functoid to count repeating field elements. It is not restricted to records.

Example:

Source Document:

- <ns0:PO xmlns:ns0="http://BTSMar13.CountInput">

- <Items>

</Items>

- <Items>

</Items>

- <Items>

</Items>

- <Items>

</Items>

- <Items>

</Items>

</ns0:PO>

Record Count Destination Document:

- <ns0:Count xmlns:ns0="http://BTSMar13.CountOutput">

- <Details>

</Details>

</ns0:Count>

Please note that Items record in the Destination Document should be unbounded as we are expecting multiple occurrences.

[ Read More ]

Value Mapping and Value Mapping Flattening Functoids:

Posted by Ravi at 9:32 PM – 0 comments

Value Mapping and Value Mapping Flattening Functoids:

Use Value Mapping and Value Mapping Flattening Functoids to allow a Boolean value to control whether another value gets mapped.

Input:

Parameter 1: Either of the strings "true" or "false", generally from the output of some other Logical functoid or from a variable Boolean field in the input instance message.

Parameter 2: A value that is output if parameter 1 is "true". This value can be from a link from a node in the source schema that represents simple content, the output from another functoid, or a constant input parameter.

Output:

Output 1: The value of the second parameter if the value of the first parameter is "true". If the value of the first parameter is not "true", the corresponding element or attribute in the output instance message is not created.

Note:

Determine whether to use the Value Mapping functoid or the Value Mapping (Flattening) functoid based on the following characteristics of the relevant portions of the source and destination schemas:

Value Mapping: When both the source and the destination schemas define parallel repeating structures between which the relevant data is mapped.

Value Mapping (Flattening): When the source schema defines a repeating structure and the destination schema defines a flat structure, such that different instances of the repeating structure in the source schema are intended to be mapped into unique elements in the flat structure in the destination schema.

Example:

<ns0:Root xmlns:ns0="http://BTSMar13.ValueMappingSource">

Source Document:

</Record>

</Record>

</Record>

</ns0:Root>

Value Mapping Destination Document:

- <ns0:Root xmlns:ns0="http://BTSMar13.ValueMappingDest">

</ns0:Root>

Please note that the Record and Field records are unbounded in Source Document. The max occurs should be set as unbounded for both Record and Field records.

Also note that Record in the Destination Document should be unbounded as we are expecting multiple occurrences of X,Y and Z.

Value Mapping Flattening Destination Document:

- <ns0:Root xmlns:ns0="http://BTSMar13.ValueMappingDest">

</ns0:Root>

Please note that the Record and Field records are unbounded in Source Document. The max occurs should be set as unbounded for both Record and Field records.

Also note that Record in the Destination Document should be unbounded as we are expecting multiple occurrences of X,Y and Z.

Name, value, X, Y, Z are defined as attributes not elements.

[ Read More ]

Wednesday, February 20, 2013

Databases in BizTalk Server

Posted by Ravi at 11:09 PM – 0 comments

Microsoft BizTalk Server installs several databases in SQL Server. The table below describes the typical usage characteristics for the BizTalk Server databases.

BizTalk Server runtime operations typically use the first four databases (BizTalk Server Management database, MessageBox databases, Tracking database, and SSO database). Depending on the BizTalk Server functionality that you use, you may have some or all of the other databases in the table.

Database	Default database name	Description
BAM Analysis	BAMAnalysis	This database contains Business Activity Monitoring (BAM) OLAP cubes for both online and offline analysis.
BAM Archive	BAMArchive	This database archives old business activity data. Create a BAM Archive database to minimize the accumulation of business activity data in the BAM Primary Import database.
BAM Notification Services Application database	BAMAlertsApplication	This database contains alert information for BAM notifications. For example, when you create an alert using the BAM portal, entries are inserted in the database specifying the conditions and events to which the alert pertains, as well as other supporting data items for the alert.
BAM Notification Services Instance database	BAMAlertsNSMain	This database contains instance information specifying how the notification services connect to the system that BAM is monitoring.
BAM Primary Import database	BAMPrimaryImport	This is the database where BAM collects raw tracking data.
BAM Star Schema	BAMStarSchema	This database contains the staging table, and the measure and dimension tables.
BizTalk Management database	BizTalkMgmtDb	This database is the central meta-information store for all instances of BizTalk Server.
BizTalk MessageBox database	BizTalkMsgBoxDb	This database is used by the BizTalk Server engine for routing, queuing, instance management, and a variety of other tasks.
BizTalk Tracking database	BizTalkDTADb	This database stores health monitoring data tracked by the BizTalk Server tracking engine.
Rule Engine database	BizTalkRuleEngineDb	This database is a repository for: Policies, which are sets of related rules. Vocabularies, which are collections of user-friendly, domain-specific names for data references in rules.
SSO database	SSODB	This Enterprise Single Sign-On database securely stores the configuration information for receive locations.
Windows SharePoint Services configuration database	User-defined	This database contains all of the global settings for the server.
Windows SharePoint Services content database	User-defined	This database contains all of the site content, such as list items and documents.

[ Read More ]

Friday, February 8, 2013

Using the Looping Functoid

Posted by Ravi at 4:50 PM – 0 comments

Create a map that utilizes the BizTalk Server Looping functoid, by taking the following steps:

1. Click the Toolbox, and then click the Advanced Functoids tab. On the map surface, between the source and destination schemas, drag and drop a Looping functoid. This functoid accepts 1 to 100 repeating source records (or data elements) as its input parameters. The return value is a reference to a single
repeating record or data element in the destination schema.
2. Connect the left side of the Looping functoid to the multiple repeating source data elements that need to be consolidated.
3. Connect the right side of the Looping functoid to the repeating destination data element that contains the standardized data structure.

Note: The records of source and destination schema's should be set as max occurs "unbounded". You will get error if you set up as Group max occurs instead of max occurs.

Looping Functoid:

Use the Looping functoid to combine multiple repeating structures in an input instance message into a single repeating structure in the output instance message.

Input:

Parameters 1 – 100: A link from at least one repeating node in the source schema, and optionally, links from other repeating nodes in the source schema.

Output:

Output 1: A link to a single repeating node in the destination schema.

The input and output links for the Looping functoid define the repeating structures in an input instance message that are combined into a single repeating structure in an output instance message. However, additional links are required from nodes within the repeating nodes in the source schema to the appropriate nodes within the repeating node in the destination schema. Without these additional links, the repeating structures are combined, but without any of the data that they contain.
For example, if there are two input links and their corresponding structures repeat 5 and 10 times, respectively, in a particular input instance message, the corresponding structure in the output instance message repeats 15 times.
The Looping functoid can be useful in a number of ways, but it is somewhat more complicated to set up correctly than many other functoids.For more information about the Looping functoid, see the Looping Functoid.
Under certain conditions, some functoids might not behave as expected when they are used in a map with a Looping functoid. If a functoid meets the following conditions, it does not produce the expected results when used with the Looping functoid:
The functoid has more than one input link.

Two or more of the functoid input links are linked to child fields of the input records to the Looping functoid, where the child fields are not siblings.

The functoid has an output link that is linked to a child field of the output record of the Looping functoid.

There is a logical filter in effect that makes the loop occur only when the logical condition evaluates to True.

Note: The Looping functoid should not be used with the Value Mapping (Flattening) functoid. If both are used together, it results in a compiled map that assumed there is no source looping dependency for the target nodes that are below the Looping functoid.

Source Schema:

- <ns0:StoreDetails xmlns:ns0="http://BTSMar13.LoopingSource">
<StoreNumber>001</StoreNumber>
<StoreName>Chicago</StoreName>
- <Orders>
- <Order>
<OrderType>Cash</OrderType>
<OrderName>Waterbottle</OrderName>
<OrderDate>2013-01-01</OrderDate>
</Order>
- <Order>
<OrderType>Card</OrderType>
<OrderName>WaterCan</OrderName>
<OrderDate>2013-01-02</OrderDate>
</Order>
- <Order>
<OrderType>Check</OrderType>
<OrderName>Watertank</OrderName>
<OrderDate>2013-01-03</OrderDate>
</Order>
</Orders>
</ns0:StoreDetails>

Destination Schema:

- <ns0:OrderDetails xmlns:ns0="http://BTSMar13.LoopingDest">
- <Order>
<StoreNumber>001</StoreNumber>
<StoreName>Chicago</StoreName>
<OrderType>Cash</OrderType>
<OrderValue>Waterbottle</OrderValue>
<OrderDate>2013-01-01</OrderDate>
</Order>
- <Order>
<StoreNumber>001</StoreNumber>
<StoreName>Chicago</StoreName>
<OrderType>Card</OrderType>
<OrderValue>WaterCan</OrderValue>
<OrderDate>2013-01-02</OrderDate>
</Order>
- <Order>
<StoreNumber>001</StoreNumber>
<StoreName>Chicago</StoreName>
<OrderType>Check</OrderType>
<OrderValue>Watertank</OrderValue>
<OrderDate>2013-01-03</OrderDate>
</Order>
</ns0:OrderDetails>

[ Read More ]

Thursday, February 7, 2013

Using the Database Lookup Functoid

Posted by Ravi at 4:38 PM – 0 comments

You can use the Database Lookup functoid by taking the following steps:

1. Click the Toolbox, and then click the Database Functoids tab. On the map surface, in between the source and destination schemas, drag and drop a Database Lookup functoid.
2. Connect the left side of the Database Lookup functoid to the inbound document node that will specify the value used in the search.
3. Configure the input parameters of the Database Lookup functoid requires four parameters to be specified either through mapping the inbound source data to the functoid or through setting constants in the functoid.
a. For the first input parameter, verify that the inbound node, connected in step 2, is the first value in the list of properties. This is a value to be used in the search criteria. It’s basically the same as the value used in a SQL WHERE clause.
b. Set the second parameter, which is the connection string for the database. The connection string must be a full connection string with a provider, machine name, database, and either account/password or a flag indicating the use of Trusted Security mode in SQL. The connection string must include a data provider attribute. A lack of data provider attribute will generate a connection error when the map tries to connect to the database.
c. Set the third parameter, which is the name of the table used in search.
d. Set the fourth parameter, which is the name of the column in the table to be used in search.
4. Again, click the Toolbox, and click the Database Functoids tab. On the map surface, after the Database Lookup functoid, drag and drop the Error Return functoid.
5. Connect the right side of the Database Lookup functoid to the left side of the Error Return functoid. Connect the right side of the Error Return functoid to the outbound schema node that is a placeholder for error messages.
6. Again, click the Toolbox, and click the Database Functoids tab. On the map surface, above the Error Return functoid, drag and drop the Value Extractor functoid for each extracted value from the return recordset. For example, if you are returning five values in the recordset, you would need five Value Extractor functoids.
7. For each Value Extractor functoid, connect the left side of the functoid to the right side of the Database Lookup functoid.
8. Configure the properties of each Value Extractor functoid to retrieve the appropriate value by specifying the column name of the extracted value. For example, if the value returned resides in a column named FirstName, you would create a constant in the Value Extractor functoid named FirstName.
9. For each Value Extractor functoid, connect the right side of the functoid to the appropriate target schema outbound node.

If you receive the below error, Please follow the below steps.

"[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied

1. Create UDL file using the below steps.

UDL File CreationThe way a UDL file is initially created depends on your Windows installation. This can be checked from the Folder shortcut menu.

Right-click on the Desktop or in the folder where you want to create the file.
Select New from the shortcut menu.
Select Microsoft Data Link if it is listed.
Windows 2000 will most likely not have Microsoft Data Link listed. If this is the case, please refer to the next section.

UDL File Creation (If Microsoft Data Link not listed)
If no Microsoft Data Link is listed, the following steps should fix this.

Right-click on the desktop, or in the folder where you want to create the file.

Select New, then Text Document.

Give the Text Document any name with a .udl extension ("Show file extensions" must be enabled in folder options).

A window will pop up warning that "If you change a filename extension, the file may become unusable. Are you sure you want to change it?" Select Yes.

You have now successfully created a UDL file.

2. double-click it. You will see The Data Link Properties dialog appears.

3. On the Provider tab, select the OLE DB provider for your platform from the OLE DB Providers list.

4. Click Next.The Connections tab appears.

5. Enter or select from the list the name of the data source.

6. Enter appropriate values in the User name and Password fields that will be used only to test the connection, not at runtime.

7. In the Select initial catalog to use field, enter the database to be used.

8. Click Test Connection. If test successful, Click OK to save the connection string to the Universal Data Link (.udl) file.

9. Open the file in notepad and use the connection string in 3.b

The above connection string should resolve the database connection issues.

[ Read More ]