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 Extractor 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 has 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.
Adv Value Extractor Functoid
Use the Adv Value Extractor 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.
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: