BizTalk Custom Pipeline to Convert Excel file to Flat file

Posted on by Bala & Vinay

This blog explains how to convert .xlsx to Flat file in BizTalk using Custom Pipeline.

Below pipeline component is generic one which can be applied to any excel files for FlatFile conversion.

Sample Input (Excel File):

Input.png

Sample Output (Flat File):

Output.png

 

Please follow below steps.

  1. Take a class library project and keep code which is present in below Download link.

Download

2) Provide strong name key, build and deploy to GAC.

3) Take a BizTalk receive pipeline and add the pipeline component (ExcelToFlatFile) to Toolbox

3.png

4) Drag and Drop the Pipeline component from toolbox to Decode stage of Receive pipeline.

4.png

5) Build BizTalk project and deploy.

6) In Admin Console, take one-way receive port and receive location.

7) In Receive pipeline, browse the pipeline “ExcelToFlatFile”

8) Provide the below information in pipeline properties.

8.png

//ConnectionString: Provide the same connection string as in the screenshot above

//SqlStatement: [Sheet1] is the sheet name in the excel sheet.

//TargetFolderLocation: Path of the output flatfile

//TempDropFolderLocation: This is temporary path you need to provide to perform some internal operations by the pipeline code.

9) Take a send port to send the result file (Success/Failure) to file locations and provide below properties. Result file will be having Success message in case of Flat file conversion is successful, otherwise it will be having exception message.

9.png

Note: Actual flat file will be stored in the path which you have provided in Receive pipeline properties.

10) Start the BizTalk application and restart host instances.

11) Drop an Excel file in Input location and you can see the Flat file in Output location

Advertisements

Using Dictionary for BizTalk Instances

Posted on by Bala & Vinay

Sometimes in BizTalk, we may need to use Dictionary for checking various conditions for multiple messages/Records of the same BizTalk Instance. This can be achieved using Dictionary ThreadStaticAttribute.

Requirement: My BizTalk Orchestration accepts envelop message (Multiple records), I need to loop through each record and insert the data in sql table. Here the condition is I should not insert duplicate records in the table as my envelope message might have duplicate records. So, in orchestration before sql insert, I need to check whether the record is already inserted.

This requirement can be achieved using Dictionary ThreadStaticAttribute.

ThreadStatic variable is scoped to the current thread that is being processed.

We need to call a .net component from BizTalk expression shape by sending Key and Value pair as input parameters.

 

Dictionary.png

 

//SampleLookup is variable name which points to the class “LookUpHelper”

//Lookup is the name of the method which is accepting two parameters.

// lookupResult is Boolen variable.

//Key, Value is some unique identification for the record, example PolicyId/BrokerId…

 

Below .net component has a method “Lookup” which accepts two input parameters and return Boolean.

  • If the record exists in Dictionary, method will return “true”.
  • If the record doesn’t exist in Dictionary, method will insert the data into Dictionary and return “false”.
  • In Orchestration, If the method return value is “true”, you can skip sql insertion.
  • If the method return value is “false”, you can proceed for sql insertion.

You need to call the lookup method for each record in loop shape.

//.Net Code

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace SampleNamespace

{

    [Serializable]

    public class LookUpHelper

    {

        //Create Dictionary

        [System.ThreadStaticAttribute]

        private static System.Collections.Generic.Dictionary<string, string> _data = new System.Collections.Generic.Dictionary<string, string>();

        public static System.Collections.Generic.Dictionary<string, string> Data

        {

            get

            {

                if (_data == null)

                {

                    _data = new System.Collections.Generic.Dictionary<string, string>();

                }

                return _data;

            }

            set

            {

                _data = value;

            }

        }

            /// <summary>

        /// Method to check if the value is already present and if not, store the value in dictionary against a key

        /// </summary>

        /// <param name=”key”>key</param>

        /// <param name=”value”>value</param>

        public Boolean Lookup(string key, string value)

        {

            if (Data.ContainsKey(key))

            {              

                return true;

            }

            else

            {               

                Data.Add(key, value);

                return false;

            }

        }

    }

}

Why BizTalk?

Posted on by Bala & Vinay

BizTalk is market leading tool in the integration world. As per some survey conducted by (https://discovery.hgdata.com/product/biztalk-server) and (https://idatalabs.com/tech/products/biztalk-server). BizTalk has almost 16% market share in Integration world. It is most from any other integration tools. 2nd leading integration tool is IBM WebSphere.

Biztalk.jpg

So, if anyone says that BizTalk is dead or no future scope, it’s just rumor nothing else. Now Microsoft has also announced that they will release new BizTalk version in every 2 years.

Microsoft Future plans about BizTalk Server

https://www.linkedin.com/pulse/microsoft-going-retire-biztalk-server-saravana-kumar?trk=v-feed&lipi=urn%3Ali%3Apage%3Ad_flagship3_feed%3B2jPRM%2FKRBgc085CMdYDx4Q%3D%3D

 The release cadence of the Microsoft Integration is as follows:

  • Major Version of BizTalk Server every 2 years
  • Minor Release of BizTalk Server every alternate year
  • BizTalk Services will follow a release cadence of 6 months

For more Details

https://blogs.msdn.microsoft.com/biztalk_server_team_blog/2016/12/15/the-future-of-biztalk-server/

No need to worry to developer or fresher, who is going to start career in BizTalk world. Now a day lots of blogs and tutorial sites are available related to BizTalk development as well as support activity which are more from any other integration tool.

Don’t confuse with below:

https://azure.microsoft.com/en-us/services/biztalk-services/

here they are talking about BizTalk Service on BizTalk Server. Here BizTalk word is used which creates confusion. They are going to replace MABS (Microsoft Azure BizTalk Services) with Azure Logic Apps. There is not mentioned anything related to BizTalk Server.

https://azure.microsoft.com/en-gb/updates/azure-biztalk-services-simplifying-our-azure-offerings/

To simplify the customer experience across our enterprise integration services, we have incorporated the Azure BizTalk Services capabilities into Logic Apps and Azure App Service Hybrid Connections. Effective May 31, 2017, Microsoft has removed Azure BizTalk Services as an option for new subscribers.

For existing customer subscriptions, BizTalk Services will be retired on May 31, 2018. All existing subscriptions should transition to Logic Apps and App Service Hybrid Connections before this date.

Above we can see that Microsoft uses word BizTalk Services which is created confusion. Here BizTalk Services is related to MABS (Microsoft Azure BizTalk Services) which is flopped after investing 4 years, so they are now moving to Logic Apps.

BizTalk RoadMap

https://redmondmag.com/articles/2016/01/04/microsoft-biztalk-server-2016-roadmap.aspx

BizTalkRoadMap.jpg

And in Business Prospective BizTalk is best integration tool.

BizTalk provides the base services to do powerful application integration across disparate systems and even organizations. Microsoft BizTalk Server is an application server that enables companies to integrate and manage automated business processes by exchanging business documents such as purchase orders and invoices between disparate applications, within or across organizational boundaries.

It’s providing following facilities in integration which are very useful.

  • It is basically an Integration server with inbuild adapters for communication. It helps in Integrating Enterprise Applications at one common point.
  • Solutions can be developed really fast with BizTalk Server. Effort spent will be less.
  • Support, Maintenance and updates are easy to manage and maintained.
  • Its provide guaranty that message will never loss in BizTalk if any technical issue occurs.
  • It has retried mechanism as well as alternative transport mechanism for message processing.
  • BizTalk is designed to operate in Multi Server Environment. We can run the application on various servers to manage load and highly Availability.
  • Advantage for customers on cost saving in terms of development time.
  • BizTalk has BAM Portal (Business Activity Monitoring Portal) to tracking the message information inside the BizTalk for the Business guys.
  • BizTalk has a number of Tools like BRE, SSO which be effectively used.
  • It can be used to communicate with wide range of Legacy Applications. There is always a scope to develop custom

 

Multiple data type for BizTalk Schema elements

Posted on by Bala & Vinay

 

Exception:

The XML Validator failed to validate. Details: The xsi:type attribute value ‘http://www.w3.org/2001/XMLSchema:double&#8217; is not valid for the element ‘http://schemas.microsoft.com/2003/10/Serialization/Arrays:Value&#8217;, either because it is not a type validly derived from the type in the schema, or because it has xsi:type derivation blocked.

SampleSchema.jpg

Explanation:

This issue is occurred when we validate the schema. Main reason is that for a element field the data type is defined more than one format e.g. in below xml file value field has data type double and string both. so when we validate this xml with schema then it throws exception.

Resolution:

When any element has more than one datatype then we need also define this in schema property as below:

Select element and open properties window of that element as below:

Expend Advanced in Properties-> Derived By->  Select Union

SampleSchemaProperties1.jpg

Now select multiple datatypes which is required in Member Types.

Note: You can not select all data types in Member Types

SampleSchemaProperties2.jpg

 

Now save the change and validate the xml with validate instance, it will working fine.