Covast to BizTalk 2010 Map Coverter

I've created a map converter that converts version 4010 transactions from Covast to BizTalk 2010.

If you're looking for additional transaction sets, versions of Covast or BizTalk please email me.  The cost of the converter is $500.

Extract the iteration from a repeating loop designated by a certain qualifier.

A while back I posted a solution that helped me extract the value from a repeating loop designated by a qualifier.  I've seen this come up many times with EDI, either you're extracting a ST or ship-to information or a REF reference information.  Although my previous solution worked it involved adding 4 functiods and changing data within the scripting functiod depending on the Qualifier.  I've simplified it to only require 3 functoids and no need for adjusting the script inside of the functoid. 


Here's an example of what your 3 functoids would look like.

1.  Add a "^" before and after the Qualifier

2.  The scope of the Cumulative Concatenate Functoid should be 1.

3.  The Scripting Functoid Input will be the Qualifier you're wanting to find and the output from the Cumulative Concatenate functoid.

4.  Finally Insert the following code into the Scripting Functoid (Inline Visual Basic .NET)

Public Function RepeatingLoopWithQualifier_1(ByVal Qualifier As String, ByVal All_Qualifiers_and_Values_Concatonated As String) As String
'All_Qualifiers_and_Values_Concatonated Expects a Commulative Concatonated String of all
'Qualifers preceded by a "^" and Values preceded by a "^" for example ^ST^912 Summer DR.^BT^4312 W. Powell
Dim QualifierLocation As Integer = All_Qualifiers_and_Values_Concatonated.IndexOf("^" + Qualifier + "^")
Dim QualifierandValueSplit As String()
'If Qualifier is found, find the corresponding Value, else Return ""
If QualifierLocation >= 0 Then
QualifierandValueSplit = All_Qualifiers_and_Values_Concatonated.Substring(QualifierLocation + 1, All_Qualifiers_and_Values_Concatonated.Length - (QualifierLocation + 1)).Split("^")
Return QualifierandValueSplit(1)
Else Return ""
End If
End Function

C#


public string RepeatingLoopWithQualifier_1(string str_Qualifier, string str_AllQualifiers_and_Values_Concatonated)
 {
string[] _ary = str_AllQualifiers_and_Values_Concatonated.Split(new char[] {'^'});
string result = "";
if (str_AllQualifiers_and_Values_Concatonated.IndexOf("^" + str_Qualifier.Trim() + "^")>-1)
for (int i=0; i< _ary.Length; i++)
{ if (_ary[i] == str_Qualifier.Trim()) result = _ary[i+1]; }
return result;
}

Execute SQL Query from within a C# function

I don't know how many times I've had to execute a stored procedure or a SQL statement from within a BizTalk map or within a C# class, so I'm putting this entry as a reminder to myself of how to do it.


//1. Create your SQL Connection
SqlConnection conn=null;
//2. Create and open a connection object
conn=new SqlConnection("Connection String Goes Here");
//3. Open the Connection
conn.Open();
//4. Create the SQL Command and assign it it a string
string strSQLCommand="SELECT * FROM TABLE";
//5. Execute the SQL Command
SqlCommand command=new SqlCommand(strSQLCommand, conn);
//6. Use ExecuteScalar() to return the first result
string returnvalue=(string) command.ExecuteScalar();
//7. Close the Connection
conn.Close();
//8. Return theValue
return returnvalue;

Having problems undeploying that pesky BizTalk Assembly?

Have you ever received the following error message when trying to undeploy a BizTalk assembly?
"Some items in the removed assembly are still being used by items not defined in the same assembly, thus removal of the assembly failed."
Make sure that items in the assembly you are trying to remove fulfill the following conditions:
1. Pipelines, maps, and schemas are not being used by Send Ports or Receive Locations
2. Roles have no enlisted parties.
Here's a query used against the mgmt database for those of you on BizTalk 2004/2006 for finding the maps and pipelines that are still deployed that might be getting in your way of undeploying:

select    'RcvPort' PortType,
        r.nvcName Port,
        item.name MapName,assem.nvcName Assembly,
        indoc_docspec_name,
        outdoc_docspec_name 
from    bts_receiveport_transform rt 
        join bts_receiveport r on 
        rt.nReceivePortID = r.nID 
        join bt_mapspec ms on
        ms.id = rt.uidTransformGUID 
        join bts_assembly assem on 
        ms.assemblyid = assem.nID
        join bts_item item on
        ms.itemid = item.id
union
select    'SendPort' PortType, 
        r.nvcName Port, 
        item.name MapName, 
        assem.nvcName Assembly,
        indoc_docspec_name,
        outdoc_docspec_name 
from    bts_sendport_transform rt
        join bts_sendport r on
        rt.nSendPortID = r.nID 
        join bt_mapspec ms on 
        ms.id = rt.uidTransformGUID
        join bts_assembly assem on
        ms.assemblyid = assem.nID
        join bts_item item on
        ms.itemid = item.id
order by Assembly, PortType, Port 

select    'SendPort' Type,
        ReceivePortName = '',
        bts_sendport.nvcName [SendPort/ReceiveLocation],
        bts_pipeline.[name]PipelineName
from    bts_sendport 
        join bts_pipeline on
        bts_sendport.nSendPipelineID = bts_pipeline.[ID]
where    bts_pipeline.[name]<> 'Microsoft.BizTalk.DefaultPipelines.XMLTransmit'
        AND  bts_pipeline.[name]<> 'Microsoft.BizTalk.DefaultPipelines.PassThruTransmit'
union
select    'ReceiveLocation' Type,
        bts_receiveport.nvcName ReceivePortName,
        adm_receiveLocation.[Name] [SendPort/ReceiveLocation],
        bts_pipeline.[name]PipelineName
from    adm_ReceiveLocation
        join bts_pipeline on
        adm_receiveLocation.ReceivePipelineID = bts_pipeline.[ID]
        join bts_receiveport on
        adm_receivelocation.receiveportid = bts_receiveport.nID
where    bts_pipeline.[name]<> 'Microsoft.BizTalk.DefaultPipelines.XMLReceive'
        AND  bts_pipeline.[name]<> 'Microsoft.BizTalk.DefaultPipelines.PassThruReceive'
order by Type, PipelineName

XPath Cheat Sheet

It seems as though every orchestration I'm trying to xpath something either into or out of a message in BizTalk.  So I'm creating a simplecheatsheetthat I can refer to.


To set a single value inside a message:
xpath(msg_YourMessage, "Node InstanceXPath") = str_YourValue;


To extract a single value from a Message:
str_YourValue = (System.String)xpath(msg_YourMessage,"string(Node InstanceXPath)");


Note: I like casting thexpathresult to a System.String so I can have complete access to all the System.String methods/properties inside of the Expression Shape


Extract a single value from a looping segment by index:
str_Xpath= System.String.Format("Node InstanceXPath[{0}]",int_Index);
xDoc =xpath(msg_YourMessage, str_Xpath);


Count the number of nodes inside the message:
int_NumberofNodes = System.Convert.ToInt32(xpath(msg_YourMessage, "count(Node InstanceXpath)"));

BizTalk FTP FTPS SFTP Adapter

I was having some problems transferring files from a very old SUN Unix server using the FTP adapter. I decided to create a new BizTalk FTP/FTPS/SFTP adapter that would be robust and allow me to connect to FTP, FTPS, and SFTP servers.

Here are the Receive Location Properties.

Explanation of properties:

FTP Type – FTP, FTPS (FTP over SSL or FTP-SSL), or SFTP (SSH File Transfer Protocol).

CRLF Mode - The CRLF Mode property applies when downloading files in ASCII mode. If CRLF Mode is set to No Alteration the transfer happens normally without alteration. A value of CRLF converts all line endings to CR+ LF. A value of LF Only converts all line endings to LF-only. A value of CR Only converts all line endings to CR-only.

FTP Trace Mode – Send a trace of the FTP session and any errors to either a File, designated by the FTP Trace path and FileName, Event Log, Both, or None.

Temporary Remote Directory – Use a temporary remote directory to transfer files then move to final location.  This ensures no files are picked up on the FTP server before being completely written out.

Transfer Mode – Binary or ASCII

Use Passive Host Address - Some FTP servers need this option for passive data transfers. In passive mode, the data connection is initiated by the client sending a PASV command to the FTP server, and the FTP server responds with the IP address and port number where it is listening for the client's connection request. When the Use Passive Host Address property is set to Yes, the IP address in the PASV response is discarded and the IP address of the remote endpoint of the existing control connection is used instead.

Authentication Mode - By setting the Authentication Mode Property to AuthTls , a secure FTP connection can be established using either SSL 3.0 or TLS 1.0. The FTP_FTPS_SFTP Adapter will automatically choose whichever is supported by the FTP server during the secure channel establishment. The FTP control port remains at the default (21). Upon connection, the channel is converted to a secure channel automatically. All control messages and data transfers are encrypted. By choosing Implicit SSL, the FTP_FTPS_SFTP Adapter

Client Certificate – The FTP_FTPS_SFTP Adapter provides the ability to use a client certificate with secure FTP (implicit or explicit SSL/TLS).

Clear Control Channel - Reverts the FTP control channel from SSL/TLS to an unencrypted channel. This may be required when using FTPS with AUTH TLS where the FTP client is behind a DSL or cable-modem router that performs NAT (network address translation). If the control channel is encrypted, the router is unable to translate the IP address sent in the PORT command for data transfers. By clearing the control channel, the data transfers will remain encrypted, but the FTP commands are passed unencrypted.

Private Key File – The FTP_FTPS_SFTP Adapter provides the ability to use a client certificate with secure FTP (implicit or explicit SSL/TLS). You may load a certificate from separate .crt (or .cer) and .pvk files and use it as the client-side SSL cert. The .pvk contains the private key. The .crt/.cer file contains the PEM or DER encoded digital certificate. Note: Client-side certificates are only needed in situations where the server demands one.

Invoice VAN FTP/SSL – By choosing yes, the FTP_FTPS_SFTP Adapter sets all the properties correctly to connect to an Inovis VAN FTP/SSL.

Tumbleweed Certificate Common Name – The FTP_FTPS_SFTP Adapter can connect, authenticate, transfer files to a Tumbleweed Secure Transport SSL FTP Server. Instead of providing a login name and password, you pass the string "site-auth" for the username, and an empty string for the password. You must also provide a client-side digital certificate -- as the certificate's credentials and validity are used to authenticate.

MODE Z – The FTP/FTPS/SFTPAdapter automatically detects if the FTP server supports MODE Z. It allows for files to be uploaded and downloaded using compressed streams.

SOCKS Version - Both SSL/TLS and non-secure FTP communications may use SOCKS4 and SOCKS5 proxies. Choose which version of SOCKS and provide the SOCKS Proxy Host Name, SOCKS Proxy Password, SOCKS Proxy Port, and SOCKS Proxy User Name.

Active Port End Range - When Active FTP Mode is used, the client-side is responsible for choosing a random port for each data connection. (Note: In the FTP protocol, each data transfer occurs on a separate TCP/IP connection. Commands are sent over the control channel (port 21 for non-SSL, port 990 for SSL).

Active Port Start Range - This property, along with Active Port End Range, allows the client to specify a range of ports for data connections when in Active mode.

SSO Affiliate -  Specify the SSO application which contains your the Username and Password for you FTP(S)/SFTP site.

Proxy Mode - The proxy scheme used by your FTP proxy server. Valid values are 0 to 8. Supported proxy methods are as follows:

Note:The Proxy Host Name is the hostname of the firewall, if the proxy is a firewall. Also, the Proxy User Name and Proxy Password are the firewall username/password (if the proxy is a firewall).


ProxyMethod = 1(SITE site)
USER ProxyUsername
PASS ProxyPassword
SITE Hostname
USER Username
PASS Password


ProxyMethod = 2(USER user@site)
USER Username@Hostname:Port
PASS Password


ProxyMethod = 3(USER with login)
USER ProxyUsername
PASS ProxyPassword
USER Username@Hostname:Port
PASS Password


ProxyMethod = 4(USER/PASS/ACCT)
USER Username@Hostname:Port ProxyUsername
PASS Password
ACCT ProxyPassword


ProxyMethod = 5(OPEN site)
USER ProxyUsername
PASS ProxyPassword
OPEN Hostname
USER Username
PASS Password


ProxyMethod = 6(firewallId@site)
USER ProxyUsername@Hostname
USER Username
PASS Password


ProxyMethod = 7
USER ProxyUsername
USER ProxyPassword
SITE Hostname:Port USER Username
PASS Password


ProxyMethod = 8
USER Username@ProxyUsername@Hostname
PASS Password@ProxyPassword

Detect FTP Authentication - Determines what combinations of FTP/FTPS/SFTP property settings result in successful data transfers. The FTP_FTPS_SFTP Adaptertries 13 different combinations of these properties: Ssl, AuthTls, AuthSsl, Port, Passive, and Use Passive Host Address. Within the FTP protocol, the process of fetching a directory listing is also considered a "data transfer". The FTP_FTPS_SFTP Adapter method works by checking to see which combinations result in a successful directory listing download. The FTP_FTPS_SFTP Adapter requires the Host Name, Username, Password, and Port and returns a string containing an XML report of the results. It is a blocking call that may take approximately a minute to run. It is executed via the following screen that opens when the ellipses is pressed.

SSH Private Key Path and File Name - Authenticates with the SSH server using public-key authentication. The corresponding public key must have been installed on the SSH server for the Username.Authentication will succeed if the matching SSH Private Key Path and File Name is provided. Load a private key from a PEM file. Private keys may be loaded from OpenSSH and Putty formats. Both encrypted and unencrypted private key file formats are supported.

SSH Private Key Passphrase – Passphrase for an encrypted SSH private key file.

If you’re running BizTalk on a 64 bit box, host the adapter on a 32 bit host. I’ll try to get a 64 bit FTP_FTPS_SFTP Adapter up here soon.

The Adapter supports dynamic sends.

Here is an example of the code needed for a dynamic send:

msg_FF = msg_FFTemp;
port_Dynamic(Microsoft.XLANGs.BaseTypes.Address) = @"FTP_FTPS_SFTP://TEST@localhost:21///";
port_Dynamic(Microsoft.XLANGs.BaseTypes.TransportType) = "FTP_FTPS_SFTP";
msg_FF(FTP_FTPS_SFTP_DynamicTransport.Username) = "TEST";
msg_FF(FTP_FTPS_SFTP_DynamicTransport.Hostname) = "localhost";
msg_FF(FTP_FTPS_SFTP_DynamicTransport.Port) = 990;
msg_FF(FTP_FTPS_SFTP_DynamicTransport.RemoteDirectory) = "/New Directory";
msg_FF(FTP_FTPS_SFTP_DynamicTransport.AuthenticationMode) = "Implicit SSL";
msg_FF(FTP_FTPS_SFTP_DynamicTransport.Password) = "test";
msg_FF(FTP_FTPS_SFTP_DynamicTransport.RemoteFileName) = "%MessageID%";
msg_FF(FTP_FTPS_SFTP_DynamicTransport.FTPType) = "FTPS";
msg_FF(FTP_FTPS_SFTP_DynamicTransport.FTPMode) = "Passive";
msg_FF(FTP_FTPS_SFTP_DynamicTransport.LogFTPSession) = "YES";
msg_FF(FTP_FTPS_SFTP_DynamicTransport.FTPTraceMode) = "Event Log";

Please email me at here if you need another setting for your particular FTP(S) or SFTP server.

The cost of the adapter is $300 per server regardless of CPUs.  There is an unlimited site license for $1500.  The source code costs $100.

You can download a trial of the 32 bit adapter here.  The 64 bit adapter is here.  The trial version will write out partial files after 45 days.