BizTalk Mapper Extensions UtilityPack: New Database Functoids

BizTalk Mapper Extensions UtilityPack: New Database Functoids

Happy to announce that today I decided to publish another release on my Mapper Extensions UtilityPack project. This time I decided to release 2 new BizTalk Database Functoids:

  • Adv Database Lookup Functoid: Use the Adv Database Lookup functoid to extract information from a database and store it as a Microsoft ActiveX Data Objects (ADO) recordset. This functoid requires the following 3 inputs: a database connection string, table name and a WHERE clause.
  • Adv Value Extractor: Use the Adv Value Extrator functoid to extract the appropriate column value from a recordset returned by the Adv Database Lookup functoid. This functoid requires two inputs parameters: a link to the Database Lookup functoid and a column name.

Basically, they have the same functionality as the default Database Lookup Functoid and Value Extractor Functoid but they have a slightly different implementation.

Unlike the default Database Lookup Functoid that have 4 input parameters

  • A lookup value
  • A database connection string
  • A table name
  • A column name for the lookup value.

The limitation of this Functoid is that you cannot have multiple conditions, like WHERE SalesPersonID = “A” and System = “B” because of the nature of how this functoid was created

The Adv Database Lookup Functoid have the goal to suppress this limitation, it will allow 3 inputs:

  • A database connection string
  • A table name
  • A WHERE clause.

In the where condition you will be able to set all the conditions you want, including a combination of AND’s and OR’s.

The Adv Value Extractor Functoid has the exact same functionality that the default Value Extractor Functoid but prepared and optimized to work with the Adv Database Lookup Functoid.

Adv Database Lookup Functoid

Use the Adv Database Lookup functoid to extract information from a database and store it as a Microsoft ActiveX Data Objects (ADO) recordset.

Parameters

The functoid takes three mandatory input parameters:

  • Parameter 1: An ActiveX Data Objects .NET (ADO.NET) connection string for a compliant data source in which to search. ODBC data sources (including DSN sources) are not supported.
    • Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DatabaseName;Data Source=SQLServerName
  • Parameter 2: The name of the table in the database in which to search;
    • tblSalesPersons
  • Parameter 3: WHERE clause (without the WHERE)
    • SourceSystemID like ‘A’ AND SSSalesPersonID = 1

The output of the functoid will be an ADO recordset.

01-BizTalk-Server-Adv-Database-Lookup-Functoid

Adv Value Extractor Functoid

Use the Adv Value Extrator functoid to extract the appropriate column value from a recordset returned by the Adv Database Lookup functoid. This functoid requires two inputs parameters: a link to the Database Lookup functoid and a column name.

Parameters

The functoid takes two mandatory input parameters:

  • Parameter 1: An ADO recordset, which is the output of the Database Lookup functoid. This recordset never contains more than one database row.
  • Parameter 2: The name of a column from which to extract a value for output.
    • TargetSalesPersonID

The output of the functoid will be the value of that column on the database table.

02-BizTalk-Server-Adv-Value-Extractor-Functoid

BizTalk Mapper Extensions UtilityPack: Project Description

BizTalk Mapper Extensions UtilityPack is a set of libraries with several useful functoids to include and use it in a map, which will provide an extension of BizTalk Mapper capabilities.

Where to download?

You can download this functoid along with all the existing one on the BizTalk Mapper Extensions UtilityPack  here:
BizTalk Mapper Extensions UtilityPack GitHub RepositoryBizTalk Mapper Extensions UtilityPack
GitHub

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community. View all posts by Sandro Pereira