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. 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. WinSCP Login Dialog .
   
   Now you will see the following window as 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.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:

• What's New in BizTalk Server 2013
• SFTP Adapter Documentation
• Bitvise SSH Server Installation Guide 
• WinSCP Latest Release and Documentation
• Understanding Key Differences Between FTP, FTPS and SFTP

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.

---

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.

---

The simplest way to GAC your project from IDE.

As a BizTalk Developer you always need to add your dlls project to Global Assembly Cashe.
The simplest way to do this procedure is by a command which you need to create it in IDE.
By doing the following procedures:
1-Open VS.NET.
2- In Tool bar click Tools
3- From Tools click External Tools..
4-In External Tools dialog box click Add button.
5-In Title field add title like Install to GAC.
6- In Command field browse a gacutil.exe file which is in
C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\gacutil.exe
it depends where you install your visual studio
7-In argument filed add the following /f /i $(TargetPath)
i means install to assembly
f means if dll exsits then overwite it
$(TargetPath) is the path if selected project
8-In initial directory field add $(TargetDir)
$(TargetDir) means directory of the selected project
9- Check Use Output window to inform you in output window the dll has been added to GAC
10- Click Ok button.


Now your cammand is ready.

Just select the project you want to add it to GAC
then select tools then Install to CAG command you created.

Hope this will Help!

How to use DebugView tool ?

When I was developing BizTalk Applications, I used to work with EvenLog command to trace BizTalk work flow actions in Development Phase.

Then in deployment phaseI go through all EventLog commands to remove them.

DebugView is a cool tool is provided from microsoft and it needs some simple steps to use it, I will show you a simple example how to use it:

1- Download the tool from http://www.microsoft.com/technet/sysinternals/Miscellaneous/DebugView.mspx

2- Extract the zip file and run Dbgview.exe

3- I created a simple windows application with button and I added the following to button event.

System.Diagnostics.Debug.WriteLine("test -" + " Debug Suleiman");
System.Diagnostics.Trace.WriteLine("test -" + " Trace Suleiman");

4- I ran the application in debug mode then I clicked the button and I found following in DebugView.exe window

5- If you want to filter the view and highlight your required event, you have to click edit then Filter/Highlight or ctrl+L shortcut.

6- In this example I included all (*) , exclude empty and Trace in green highlight in Filter1 & Debug in purple highlight as the following:


7- If I convert my application to release mode you and click button again, will find that just Trace will be viewed

Conclusion:

Event logging uses disk space and normally takes processor time away from applications. So do not store unimportant information or large quantities of data in the event log. Generally, an event log should be used to log unexpected conditions or errors and not user actions or performance tracking information.

use System.Diagnostics.Debug.WriteLine("") will be viewed in Debug mode
Debug command does not work with Release Mode
System.Diagnostics.Trace.WriteLine will be viewed in both Release & Debug modes

You can use DebugView to check your required actions and you don't need to remove your trace lines in deployment phase. and your actions will be viewed by this cool tool

There are filter option and you can import/export your trace logs

Unable to run Biztalk 2009 Project using Visual Studio 2008

I was trying to run a BizTalk Project using Visual Studio 2008 but I got the following error message:

The project file btproj cannot be opened.
The project type is not supported by this installation.

Causes:

BizTalk Developer tools and SDK was not installed

Solution:
You need to modify the the BizTalk 2009 installation and check Developer tools & SDK option

I hope this will help :)

How to install BizTalk 2009 in Multicomputer environment?

It took me 3 days to install BizTlak 2009 in Multicomputer environment because I faced many problems to install.
I think if you follow the following steps, you will need about a day to finish installation.

1- You need to ask customer to prepare a domain windows user
and the following windows domain groups:
-SSO Administrators
-SSO Affiliate Administrators
-BizTalk Server Administrators
-BizTalk Server Operators
-BizTalk Application Users
-BizTalk Isolated Host Users
2- Install Sql Server 2008 in the database server
3- Install Sql Server 2008 client tool in BizTalk Server
4- Install DTC service in BizTalk server and Database Server.
5- Configure DTC to allow remote client in BizTalk & Database servers
6- Now you are ready to install BizTlak server 2009 software
7- You have to cinfigure databases of BizTalk with the domain users & groups which are created before.

How to Add BizTalk Project to Team Foundation Server TFS?

Unfortunately, you can't create biztalk project to TFS .
Once I decided to try a tricky work around solution which is to add existing biztalk project to TFS solution.Then, you can use all TFS tools with your BizTalk Project.
I tried it and I don't believe myself how I didn't try it ages ago.

I hope you get benifits from my tricky soltuion