Friday, November 29, 2013

How to Extract Email Attachments By Pipeline


Introduction

In this article I will demonstrate step by step instructions of how to extract email attachments from POP3 adapter by using custom receive pipeline.

Problem Statement

You can have some customers that need to integrate with your integration middle-ware using POP3. So, They will send emails with multiple attachments and these attachments represent the messages that you need to process.

Solution

There are 2 BizTalk solutions that we can implement either in Message Level or Orchestration level. As performance perspective, a message level is much better because orchestration level has much persistence points which can reduce the performance of our integration solution.
If you would like to see the orchestration solution you can check this link.

To process email attachments through message level, then we need to process them using pipeline.
So, we need to implement custom receive pipeline and add it to disassemble part of receive pipeline
For now let's start our walk-though demonstration.

Create Custom Receive Pipeline


  1. Create a new solution as shown in figure 1

    Figure 1- Creating a new Solution

  2. Create a new class library projects as shown in figure 2

    Figure 2- Creating a new Class library project

  3. Remove Class1.cs file
  4. Create a new class as shown in figure 3

    Figure 3- Creating a new class

  5. Add Microsoft.BizTalk.Pipeline.dll from this path %BTSINSTALLPATH%
  6. Add Microsoft.BizTalk.Pipeline.Components.dll as a reference from this path %BTSINSTALLPATH%\Pipeline Components
  7. Implement the IBaseComponent, IComponentUI interfaces as shown in figure 4
    namespace TechNetWiki.AttachmentsExtractor.PipelineComponent
    {
        using System;
        using System.IO;
        using System.Text;
        using System.Reflection;
        using System.ComponentModel;
        using Microsoft.BizTalk.Message.Interop;
        using Microsoft.BizTalk.Component.Interop;
        using System.Runtime.InteropServices;
      
        [ComponentCategory(CategoryTypes.CATID_PipelineComponent)]
        [ComponentCategory(CategoryTypes.CATID_DisassemblingParser)]
        [Guid("01F04E58-7AD8-42EF-A3CB-D939B960E8F9")]
        public class AttachmentsExtractor : IBaseComponent, IComponentUI
        {
            #region IBaseComponent Members
            /// <summary>
            /// Gets Description of the component
            /// </summary> 
            public string Description
            {
                get
                {
                    return "Email Attachments Extractor";
                }
            }
      
            /// <summary>
            /// Gets Name of the component
            /// </summary> 
            public string Name
            {
                get
                {
                    return "EmailAttachmentsExtractor";
                }
            }
      
            /// <summary>
            /// Gets Version of the component
            /// </summary>
            public string Version
            {
                get
                {
                    return "1.0";
                }
            }
      
            #endregion
      
            #region IComponentUI members
            /// <summary>
            /// Component icon to use in BizTalk Editor
            /// </summary>    
            public IntPtr Icon
            {
                get
                {
                    return System.IntPtr.Zero;
                }
            }
      
            /// <summary>
            /// The Validate method is called by the BizTalk Editor during the build
            /// of a BizTalk project.
            /// </summary>
            /// <param name="obj">An Object containing the configuration properties.</param>
            /// <returns>The IEnumerator enables the caller to enumerate through a collection of strings containing error messages. These error messages appear as compiler error messages. To report successful property validation, the method should return an empty enumerator.</returns>
            public System.Collections.IEnumerator Validate(object obj)
            {
                return null;
            }
            #endregion
        }
    }
    Figure 4-Implementing  IBaseComponent, IComponentUI

  8. Implement IDisassemblerComponent interface which is mandatory to implement GetNext and Disassemble methods 
    • GetNext method returns messages resulting from the disassemble method execution 
    • Disassemble method used to split or process the incoming message document
  9. When we receive an email we will receive it as a message associated with multi-parts messages so In Disassemble method we need to loop in each Part of the message and create a new message.We will will start looping from index 1 because index zero is the body content which we are not interested as shown in figure 5
    #region IDisassemblerComponent members
            /// <summary>
            /// Returns messages resulting from the disassemble method execution
            /// </summary>
            /// <param name="pc">the pipeline context</param>
            /// <returns></returns>
            public IBaseMessage
                GetNext(IPipelineContext pc)
            {
                // get the next message from the Queue and return it
                IBaseMessage msg = null;
                if ((_msgs.Count > 0))
                {
                    msg = ((IBaseMessage)(_msgs.Dequeue()));
                }
                return msg;
            }
            /// <summary>
            /// called by the messaging engine when a new message arrives
            /// </summary>
            /// <param name="pc">the pipeline context</param>
            /// <param name="inmsg">the actual message</param>
            public void Disassemble(Microsoft.BizTalk.Component.Interop.IPipelineContext pc, Microsoft.BizTalk.Message.Interop.IBaseMessage inmsg)
            {
                var partName = string.Empty;
                // we start from index 1 because index zero contain the body of the message
                // which we are not interested
                for (int i = 1; i < inmsg.PartCount; i++)
                {
                    IBaseMessagePart currentPart = inmsg.GetPartByIndex(i, out partName);
                    Stream currentPartStream = currentPart.GetOriginalDataStream();
                    var ms = new MemoryStream();
                    IBaseMessage outMsg;
                    outMsg = pc.GetMessageFactory().CreateMessage();
                    outMsg.AddPart("Body", pc.GetMessageFactory().CreateMessagePart(), true);
                    outMsg.Context = inmsg.Context;
                    currentPartStream.CopyTo(ms);
                    string attachmentContent = Encoding.UTF8.GetString(ms.ToArray());
                    MemoryStream attachmentStream = new System.IO.MemoryStream(
                    System.Text.Encoding.UTF8.GetBytes(attachmentContent));
                    outMsg.GetPart("Body").Data = attachmentStream;
                    outMsg.Context.Write("ReceivedFileName", "http://schemas.microsoft.com/BizTalk/2003/file-properties", partName);
                    _msgs.Enqueue(outMsg);
                }
            }
            #endregion
    Figure 5- Implementing IDisassemblerComponent interface

    the Full code of custom pipeline as show in figure 6

    namespace TechNetWiki.AttachmentsExtractor.PipelineComponent
    {
        using System;
        using System.IO;
        using System.Text;
        using System.Reflection;
        using System.ComponentModel;
        using Microsoft.BizTalk.Message.Interop;
        using Microsoft.BizTalk.Component.Interop;
        using System.Runtime.InteropServices;
        [ComponentCategory(CategoryTypes.CATID_PipelineComponent)]
        [ComponentCategory(CategoryTypes.CATID_DisassemblingParser)]
        [Guid("01F04E58-7AD8-42EF-A3CB-D939B960E8F9")]
        public class AttachmentsExtractor : IBaseComponent, IComponentUI, IDisassemblerComponent
        {
            #region IBaseComponent Members
            /// <summary>
            /// Gets Description of the component
            /// </summary>  
            public string Description
            {
                get
                {
                    return "Email Attachments Extractor";
                }
            }
            /// <summary>
            /// Gets Name of the component
            /// </summary>  
            public string Name
            {
                get
                {
                    return "EmailAttachmentsExtractor";
                }
            }
            /// <summary>
            /// Gets Version of the component
            /// </summary>
            public string Version
            {
                get
                {
                    return "1.0";
                }
            }
            #endregion
            #region IComponentUI members
            /// <summary>
            /// Component icon to use in BizTalk Editor
            /// </summary>     
            public IntPtr Icon
            {
                get
                {
                    return System.IntPtr.Zero;
                }
            }
            /// <summary>
            /// The Validate method is called by the BizTalk Editor during the build
            /// of a BizTalk project.
            /// </summary>
            /// <param name="obj">An Object containing the configuration properties.</param>
            /// <returns>The IEnumerator enables the caller to enumerate through a collection of strings containing error messages. These error messages appear as compiler error messages. To report successful property validation, the method should return an empty enumerator.</returns>
            public System.Collections.IEnumerator Validate(object obj)
            {
                return null;
            }
            #endregion
            /// <summary>
            /// this variable will contain any message generated by the Disassemble method
            /// </summary>
            private System.Collections.Queue _msgs = new System.Collections.Queue();
            #region IDisassemblerComponent members
            /// <summary>
            /// Returns messages resulting from the disassemble method execution
            /// </summary>
            /// <param name="pc">the pipeline context</param>
            /// <returns></returns>
            public IBaseMessage
                GetNext(IPipelineContext pc)
            {
                // get the next message from the Queue and return it
                IBaseMessage msg = null;
                if ((_msgs.Count > 0))
                {
                    msg = ((IBaseMessage)(_msgs.Dequeue()));
                }
                return msg;
            }
            /// <summary>
            /// called by the messaging engine when a new message arrives
            /// </summary>
            /// <param name="pc">the pipeline context</param>
            /// <param name="inmsg">the actual message</param>
            public void Disassemble(Microsoft.BizTalk.Component.Interop.IPipelineContext pc, Microsoft.BizTalk.Message.Interop.IBaseMessage inmsg)
            {
                var partName = string.Empty;
                // we start from index 1 because index zero contains the body of the message
                // which we are not interested
                for (int i = 1; i < inmsg.PartCount; i++)
                {
                    IBaseMessagePart currentPart = inmsg.GetPartByIndex(i, out partName);
                    Stream currentPartStream = currentPart.GetOriginalDataStream();
                    var ms = new MemoryStream();
                    IBaseMessage outMsg;
                    outMsg = pc.GetMessageFactory().CreateMessage();
                    outMsg.AddPart("Body", pc.GetMessageFactory().CreateMessagePart(), true);
                    outMsg.Context = inmsg.Context;
                    currentPartStream.CopyTo(ms);
                    string attachmentContent = Encoding.UTF8.GetString(ms.ToArray());
                    MemoryStream attachmentStream = new System.IO.MemoryStream(
                    System.Text.Encoding.UTF8.GetBytes(attachmentContent));
                    outMsg.GetPart("Body").Data = attachmentStream;
                    outMsg.Context.Write("ReceivedFileName", "http://schemas.microsoft.com/BizTalk/2003/file-properties", partName);
                    _msgs.Enqueue(outMsg);
                }
            }
            #endregion
        }
    }
    Figure 6-     Custom pipeline full code


  10. Build the project then copy the generated TechNetWiki.AttachmentsExtractor.PipelineComponent.dll to %BTSINSTALLPATH%\Pipeline Components

Create Receive Pipeline

  1. Create a new BizTalk Project by right click solution and select new project then select Empty BizTalk Server Project and name it TechNetWiki.AttachmentsExtractor.Pipelines as shown in figure 7

    Figure 7- Create Pipeline BizTalk Project

  2. Right click TechNetWiki.AttachmentsExtractor.Pipelines project -> Select Add-> New Item -> Select Receive Pipeline-> Name it AttachmentsExtractorPipeline as shown in figure 8 

    Figure 8- Create a new receive pipeline

  3. Right click to ToolBox window ->select Choose Items... -> select BizTalk Pipeline Components -> check EmailAttachmentsExtractor as shown in figure 9

    Figure 9- Add pipeline component to ToolBox

  4. Drag drop EmailAttachmentsExtractor to Disassemble part as shown in figure 10 

    Figure 10- Setting pipeline component in Disassemble part

  5. Right click project TechNetWiki.AttachmentsExtractor.Pipelines -> select Properties -> select Sign tab-> check Sign the assembly -> choose a strong name key file -> select New and rename it to key.snk -> select Deployment tab -> set Application Name to TechNetWiki.AttachmentsExtractor
  6. Right click TechNetWiki.AttachmentsExtractor.Pipelines -> select Deploy

Configure POP3 Adapter

  1. Open BizTalk Server Administration Console 
  2. Right click TechNetWiki.AttachmentsExtractor - Receive Port -> select New -> One-way Receive Port... -> change name to Rcv_EmailTest -> select Receive Location tab -> change name to RcvLoc_EmailTest
  3. Select POP3 adapter-> click Configure button -> set POP3 adapter configure as shown in figure 11 In this demo I am using my hotmail account to configure POP3 adapter

    Figure 11 - Configuring POP3 adapter

  4. Create a new send port and name it Snd_EmailTest -> select File Adapter and configure it as shown in figure 12
    figure 12- Creating send port

  5. Select Filters tab then set filter to our receive port name as shown in figure 13

    Figure 13- Setting filter

Test the Solution

  1. Send email with sample attachment message to the configured email that we configured on POP3 adapter which is my demo YouUserName@hotmail.com
  2. Check send port location

Sample Code

You can find the sample source code in the following link

Conclusion

In BizTalk Server there are two ways to extract email attachments either using custom receive pipeline or orchestration

In this article, I walked-through in step by step how to create a custom receive pipeline to extract email attachments.

See Also

Read suggested related topics:
Another important place to find a huge amount of BizTalk related articles is the TechNet Wiki itself. The best entry point is BizTalk Server Resources on the TechNet Wiki.
Posted by at No comments:
Labels: , , , , , , , , , , , ,

An Easy Way to Get BizTalk Alerts


Introduction

As a BizTalk administrator, you need to know about any issues in your BizTalk environment. It is crucial to know about these issues before they become real problems or cause any unnecessary outage for any integration services provided by your BizTalk server.

In this article I will demonstrate an easy way to send alerts in case there are any errors or warnings in the BizTalk environment.

Problem Statement

You may have some customers connecting to your integration middle-ware through receive adapters such as POP3, SFTP, FTP, File, etc. In some cases you may get some warnings and errors which affect these receive adapters and make them disabled once the error threshold is exceeded.

In many instances you would not be able to use the BizTalk Administration Console for alerting as these entries do not even register in the BizTalk Message Box.

Therefore, it is very important to be notified before the end users prompted with warning or error messages when they are trying to upload into integration middle-ware. It is equally important to take a proactive approach to resolve any upcoming issues you might be experiencing with your BizTalk server.

Solution

You might be attempted to use monitoring tools like SCOM or BizTalk360,..These tools are not free and some companies might find them costly to licence and implement.

If your project does not have a budget to deploy these motioning tools, there is an easy way to get BizTalk alerts by using a combination of PowerShell scripting and the built-in Windows Task Scheduler through the following steps:


  1. Select Windows Start Button ->Control Panel->Administrative Tools -> Task Scheduler as shown in Figure 1 or use the short cut command in run window control schedtasks

    Figure 1 - Accessing Task Scheduler
  2. In Task Scheduler window select Microsoft folder->click on create task -> in General Tab Name the task as shown in Figure 2

    Figure 2- Create Task
  3. Select Trigger Tab->Click New button-> in the Begin the task select on an event ->select custom->click New Event  Filter button-> in Event Sources select BizTalk -> In case you want to filter by using event id you can set event id as shown in Figure 3 
    Figure 3- Create Trigger

  4. Select Action Tab->Click New Button->In Action select Send an e-mail->set email settings properties as shown in Figure 4

    Figure 4 - Create Exchange Email Action
  5. If you want to to add attachment of event logs to be included in email, then you need to create a new action to run the following command  which write your filter log into a text file in my case I filtered on EventId 5740 which is pop3 warning that happens once pop3 user not authenticated and save it in this path C:\SendEmail\temp\logs.txt
    del C:\SendEmail\temp\logs.txt
    wevtutil qe Application "/q:*[System [(EventID=5740)]]" /f:text /rd:true /c:1> C:\SendEmail\temp\logs.txt
  6. Save command into cmd file and rename it into path C:\SendEmail\WriteLog.cmd
  7. Create new action -> In Action field select start a program-> Set Program/Script Path as shown in Figure 5

    Figure 5 - Create Command Action

  8. Sort action and make sure that running the command action in the first action as shown in Figure 6

      Figure 6- Sort actions

  9. In case you do not want to use exchange mail server, then you can write the following powershell script and save it in this path C:\SendEmail\SendEmailScript.ps1
    $emailSmtpServer="smtp.gmail.com"
    $emailSmtpServerPort="587"
    $emailSmtpUser="yourusername@gmail.com"
    $emailSmtpPass="yourpassword"
    $emailFrom="fromusername@gmail.com"
    $emailTo="sendtousername@gmail.com"
    $emailMessage=New-Object System.Net.Mail.MailMessage( $emailFrom , $emailTo )
    $emailMessage.Subject="BizTalk Error"
    $emailMessage.IsBodyHtml=$true
    $emailMessage.Body=@"
    <p>There is a BizTalk issue</p>
    <p>Check the attachment</p>
    "@
    $emailAttachment=New-Object System.Net.Mail.Attachment("C:\SendEmail\temp\logs.txt")
    $emailMessage.Attachments.Add($emailAttachment)
    $SMTPClient=New-Object System.Net.Mail.SmtpClient( $emailSmtpServer , $emailSmtpServerPort )
    $SMTPClient.EnableSsl=$true
    $SMTPClient.Credentials=New-Object System.Net.NetworkCredential( $emailSmtpUser , $emailSmtpPass );
    $SMTPClient.Send( $emailMessage )
  10. Create another command file to run powershell script as below and save it in this path C:\SendEmail\RunPowerShellScript.cmd
    powershell C:\SendEmail\SendEmailScript.ps1

    Now SendEmail folder should be as shown in Figure 7


    Figure 7- Files inside SendEmail Folders

  11. Update Send Email Action which we created in step 4 to run a program with path C:\SendEmail\RunPowerShellScript.cmd as shown in 
    Figure 8- Edit Action to use PowerShell script
  12. To test your work, create pop3 receive location and set a wrong username and password

Conclusion 

In this article, I walked-through in step by step how to use an easy way to send any BizTalk alerts to specific email using a built-in Windows Task Scheduler .


Another important place to find a huge amount of BizTalk related articles is the TechNet Wiki itself. The best entry point is BizTalk Server Resources on the TechNet Wiki.
Posted by at No comments:
Labels: , , , , , , , , , ,

How to Configure SFTP Send Port Dynamically in BizTalk Server 2013


Introduction

SFTP Adapter is a new adapter added to the built-in adapters in BizTalk Server 2013. I explained how to use SFTP adapter in article BizTalk Server 2013: How to use SFTP Adapter. In that article I explained how to prepare SFTP testing environment and how to configure SFTP adapter in design time. In this article I will walk-through in how to configure SFTP send port dynamically at run-time.

Problem

You have an orchestration that send messages to a specific customer via SFTP adapter. However, your integration middleware start to be expanded and start to have multiple types of customers that need to receive messages in different SFTP addresses. So, how we can update our orchestration to enable it to send messages to dynamic addresses.

Solution

We need to create a dynamic send port and configure it at run time.Dynamic ports allow the physical location of a physical send port (one-way or solicit-response) to be determined at run time. The ability to specify the transport protocol and address at run time allows for the flexibility of routing messages depending on message content or message context. We can configure the send port to be a dynamic within the orchestration and we can save and retrieve properties of SFTP adapter configuration information from our configuration DB or Business Rule Engine (BRE)

Step By Step Implementation

  • Create a source schema (CustomerInfo) as shown in figure 1
    Figure 1. CustomerInfo Schema Structure

  • Create a schema for BRE settings as shown in figure 2, in my demo I just want Address, username and password we can add more if you want to use digital certificate
    Figure 2. Sftp Bre Schema Structure
  • By using Business Rule Composer create the SFTP Dynamic Policy and rule for each customer as shown in Figure 3, 4, 5 then Publish and Deploy policy
    Figure 3. Customer A Rule
    Figure 4. Customer B Rule
    Figure 5. Customer C Rule

  • Create an orchestration that processes a message and send it to a dynamic port, I created a sample orchestration as show in figure 6
    Figure 6. Business Process Orchestration
  • Define the following variable and messages as the following table
    Message / Variable Identifier Type Comments
    Message msgIn TechNetWiki.DynamicSftpPort.CustomerInfo The incoming message
    Message msgOut TechNetWiki.DynamicSftpPort.CustomerInfo The outgoing message
    Message msgSftpBre TechNetWiki.DynamicSftpPort.SftpBreSchema The Sftp Bre info message
    Variable varSftpBreXmlDoc System.Xml.XmlDocument A variable to load msgSftpBre message
  • Inside Assign_SftpBre assignment shape we need create xml document as the following code
    varSftpBreXmlDoc.LoadXml(@"<ns0:SftpInfo xmlns:ns0='http://TechNetWiki.DynamicSftpPort.SftpBreSchema'>
      <Address></Address>
      <UserName></UserName>
      <Password></Password>
      <CustomerId></CustomerId>
    </ns0:SftpInfo>");
    msgSftpBre = varSftpBreXmlDoc;
    msgSftpBre.CustomerId=msgIn.CustomerId;

  • Inside AssignDynamicSftpInfo assignment shape we will set the values retrieved from BRE to fill the msgOut and DynamicSFTPPort properties
    msgOut=msgIn;
    msgOut(SFTP.UserName)=msgSftpBre.UserName;
    msgOut(SFTP.Password)=msgSftpBre.Password;
    msgOut(SFTP.AccessAnyServerHostKey)=true;
    DynamicSFTPPort(Microsoft.XLANGs.BaseTypes.Address)=msgSftpBre.Address + "%MessageID%.xml";

  • Create a send port in configuration wizard Set Port binding to Dynamic as shown in figure 5
    Figure 7. Set Dynamic Port Binding
  • Deploy your project and create file receive port/location then configure orchestration then enlist and start the application.
  • Test the solution by adding some sample files with different customer id.In this demo I created three folders A,B,C inside the SFTP server to test the output messages.
  • You can check BizTalk Server 2013: How to use SFTP Adapter to know how to prepare sftp testing environment

Sample Code

You can find the source code belonging to this article at MSDN Code Gallery:BizTalk Server 2013: How to configure SFTP Send Port Dynamically

Conclusion

In this article, we showed how to create a dynamic sftp send port inside orchestration and how to configure it at run time by using Business Rule Engine.

See Also

Read suggested related topics:
Another important place to find a huge amount of BizTalk related articles is the TechNet Wiki itself. The best entry point is BizTalk Server Resources on the TechNet Wiki.
Posted by at No comments:
Labels: , , , , , , , , , , , , ,

Wednesday, November 27, 2013

How to use SFTP Adapter using BizTalk Server 2013



Introduction 

BizTalk server has supported the FTP (File Transfer Protocol) adapter since its early release. 
With the BizTalk 2010 release, Microsoft enhanced the existing FTP adapter to support SSL (FTPS), which encrypts the data transfers between systems. In the BizTalk 2013 release, Microsoft added SFTP (SSH File Transfer Protocol also called Secure File Transfer Protocol). This has been much anticipated over the years and it is great that a true SFTP adapter now exists with BizTalk server. In the past for one had to rely on the open source adapter developed by Blogical found on CodePlex or an adapter from commercial third parties (like /n software SFTP). Now you will have advantages of Microsoft support for out-of-the-box of SFTP adapter.
For more information of BizTalk Server 2013 new features check this link
http://www.microsoft.com/en-us/biztalk/whats-new.aspx
In this article I will demonstrate step by step of the followings:
  • How to prepare a testing environment to communicate with the SFTP adapter as Figure 1?
  • How to configure BizTalk  SFTP Adapter?
  • How to test BizTalk Adapter?
Figure 1
Figure 1. Demo Architecture


How to prepare a testing environment to communicate with the SFTP adapter?

  1. Create a new local user Control Panel -> Administrative Tools -> Computer Management -> Local Users and Groups -> right click User -> New User-> Specify a User name and password.

  2.  Switch and Login for one time using SFTPUser to create user related folder and files.
  3. Download Windows SFTP server tool that allows you to connect, retrieve and put files to try out the functionality of the SFTP Adapter.There are many  tools For this demo I am using the following one http://dl.bitvise.com/BvSshServer-Inst.exe ,Then install it with default settings and restart your machine and make sure that Bitvise SSH Server service is started.
  4. Download any SFTP client tool in this demo I am using the following tool http://winscp.net/download/winscp517setup.exe .Then install it with default settings and restart your machine.
  5. Open WinSCP and enter Host Name , username and password as figure 2 then click login button then click yes in warning dialog .

    Figure 2

    Figure 2. WinSCP Login Dialog .

    Now you will see the following window as Figure 3.

    Figure 3
    Figure 3. WinSCP Start-up window


  6. Create a new Directory  by selecting Files-> New -> Directory as figure 4.

    Figure 4.Creating new Folder in WinSCP

  7. In create Folder window enter folder name in this demo I named it MessageTesting and check Set permissions as figure 5.

    Figure 5 
    Figure 5.Set Folder Permissions in WinSCP
  8. Create folder IN and OUT inside folder MessageTesting same settings of Step 7 and the path for receive location should be as figure 6.
    Figure 6.Path of IN Folder in WinSCP

    Now we are ready to configure the BizTalk SFTP Adapter.

How to configure BizTalk SFTP Adapter?

  1. ​Create a receive port I name it Rcv_SFTP then create receive location Rcv_SftpLoc then choose type of receive location to SFTP from drop down list then click Configure then change AcceptAnySSHServerHostKey property to True then fill other settings as Figure 7.
    Figure 7.Configuring Receive SFTP Adapter

    You can copy path of sftp folder to clipboard as figure 8.


    Figure 8.Copy Path to Clipboard in WinSCP

  2. Create Send port and Name Snd_SFTP select type from drop down list  SFTP then click Configure then fill setting as Figure 9.
    Figure 9.Send SFTP Adapter Configuration

  3. Configure Filter of send port subscribe messages from port Rcv_SFTP as figure 10.


    Figure 10.Filter SFTP send port Configuration
  4. Enable receive location and Start send port.

How to test BizTalk Adapter?

  1. Copy any file to IN folder path by drag drop from left window panel to right panel as Figure 11.

    Figure 11.Copy dialog window in WinSCP
  2. Check OUT folder for generated file as Figure 12
Figure 12.Copy dialog window in WinSCP

Conclusion


In this demonstration, we investigated:
  •  The receive and send SFTP adapter, which is a new Feature comes with BizTalk Server 2013.
  •  The step by step of preparation of an environment for testing the transfer of messages using the SFTP adapter.
  •  And how to configure and test SFTP adapter.

See Also

Read suggested related topics:
Another important place to find a huge amount of BizTalk related articles is the TechNet Wiki itself. The best entry point is BizTalk Server Resources on the TechNet Wiki.
Posted by at No comments:

Receive and Send Excel Files from Existing Templates using BizTalk Server



Introduction

As a service provider that we provide a service to multiple customers and channels.
There is a new requirement that we are expecting to receive from each customer a specific excel file and sending a different excel file.
You can imagine that we could receive hundreds of different excel files from customers.
Also, we have an integration point for each customer that we need to map the customer format file to our canonical schema.

BizTalk Solution Approach


We need to build two pipelines one for receiving and one for sending excel files.
For receiving we need to parse an excel file using the configured template file to generate an xml file matching an existing customer schema as demonstrated in Figure 1
Figure 1. Receiving Excel Pipeline Diagram
For sending, we need to convert xml file to generate an excel file via custom sending pipeline using an existing excel template as demonstrated in figure 2
Figure 2. Sending Excel Pipeline Diagram



What is the best excel API to be used?



There are some of free excel APIs that you can use to process excel file like ExtremeML, Microsoft Excel Object Library, epplus and NPOI.I read a lot of articles and reviews and I tried to do some of POCs for eachI concluded that the best API I can be used and support both xls and xlsx extensions with the best performance is by using NPOI API.
You can find the api in this link http://npoi.codeplex.com


How to Prepare Excel template file?



I will give one example how to prepare a template file from a sample of customer excel file.

Let's say we got the file from customer A as Figure 3

Figure 3. Sample Excel Document

We need to know where the location of repeated records and where are the other fields.
First we need to prepare the template manually by making a place holder for each column name of repeated records and a label as a comment (DATAROW) where we can find the first repeated record then the place holders of other fields like figure 4



Figure 4. Preparing Excel Template Document

Then we need to generate a schema depending on a template file that we build it on figure 4
I build a simple tool to generate a schema from an existing template  as Figure 5 or you can create schema manually



Figure 5. Tool to generate xsd from Existing Template


The tool will generate the schema as Figure 6


Figure 6. Generated xml scehma





How to configure the excel processor custom pipeline?




I checked NPOI api examples and I built my own custom pipeline component ExcelProcessor.BizTalk.PipelineComponents.dll and I copy it to this folder %BTSInstallPath%/Pipeline Components and you need to add any dependents dlls to GAC like the following dlls:

ICSharpCode.SharpZipLib.dll
NPOI.dll
NPOI.OOXML.dll
NPOI.OpenXml4Net.dll
NPOI.OpenXmlFormats.dll


for more information check Developing Custom Pipeline Components

Then you need to add a new receive and send pipeline then right click to Toolbox then select choose items and select  as Figure 7



 Figure 7. Adding excel decoder and encoder

Open Your receive pipeline then add ExcelPipelineDecoder from ToolBox and drag it to Decode part as Figure 8

Figure 8. Excel Receive Pipeline

Open Your send pipeline then add ExcelPipelineEncoder from ToolBox and drag it to Encode part as Figure 9


Figure 9. Excel Send Pipeline
Now your pipelines are ready to be deployed. Deploy your BizTalk project then open BizTalk Administration to configure pipelines
For testing purposes, I just added CustomerA.xsd and receive and send pipelines as Figure 10


Figure 10. BizTalk Solution Explorer


Now I will create one receive port as Rcv_Excel and a receive location as Rcv_ExcelLoc
and I will configure the Rcv_ExcelLoc as figure 11


Figure 11. Configuring receive port

Then we need to configure path of the template and TargetNameSpace of schema that related to excel template for ExcelReceivePipeline as figure 12


Figure 12. Configuring custom receive pipeline properties

for testing purposes I created a send port with XmlTransmit Send Pipeline and filtered it to BTS.ReceivePortName==Rcv_Excel
and it generates the following output as figure 13


Figure 13. Generated xml file




Conclusion



In this article I demonstrated the BizTalk approach for receiving and sending excel documents using existing templates for multiple customers.

You can use this approach for building BizTalk pipelines from existing templates for Excel, pdf ,word and any type of templates.

The challenge is how to work and deal with APIs then this approach will be easy to maintain and reuse.




See Also


Another important place to find a huge amount of BizTalk related articles is the TechNet Wiki itself. The best entry point is BizTalk Server Resources on the TechNet Wiki.
Posted by at 3 comments:
Subscribe to: Comments (Atom)