Error:

The adapter failed to transmit message going to send port “sp_Test_SFTP” with URL “sftp://sftp.mycompany.com:22/%MessageID%.xml”. It will be retransmitted after the retry interval specified for this Send Port. Details:”System.IO.FileLoadException: Could not load file or assembly ‘WinSCPnet, Version=1.2.10.6257, Culture=neutral, PublicKeyToken=2271ec4a3c56d0bf’ or one of its dependencies. General Exception (Exception from HRESULT: 0x80131500)
File name: ‘WinSCPnet, Version=1.2.10.6257, Culture=neutral, PublicKeyToken=2271ec4a3c56d0bf’ —> System.Exception: SFTP adapter requires WinSCP to be installed. Please refer http://go.microsoft.com/fwlink/?LinkID=730458&clcid=0x409 . —> System.IO.FileNotFoundException: Could not load file or assembly ‘file:///C:\Windows\Microsoft.Net\assembly\GAC_MSIL\Microsoft.BizTalk.Adapter.Sftp\v4.0_3.0.1.0__31bf3856ad364e35\WinSCPnet.dll’ or one of its dependencies. The system cannot find the file specified.

Solution:

I think BizTalk requires a specific older version of WinSCP and you can’t just download the latest one.
This error could also occur if you didn’t copy the file to the proper directly.

I found Michael Stephenson’s BizTalk 2016 SFTP blog, and it had a nice PowerShell script.

I just had to
1) Change directory names (I had a Software directory similar to him, but I had to create a WinSCP subdirectory under it)
2) Then PowerShell with the “Run as Admin” option.

Then the script ran fine, I restarted BizTalk Host Instance, and it got past this error, on to other errors that I’ll blog about in the near future.

By the way, I was able to run in a 64-bit host with no problem.  I remember that in older version of BizTalk the FTP adapter ran only in 32-bit hosts.

 

 

 

I have documented some of the questions/issues in this BAM issues post in StackOverflow.

BAM Activity shows BAM.xla “variable not defined” Error in Excel on lvwTraces

I found the solution for this in this blog: “BAM ‘variable not defined” Error in Excel“.  But for some reason, that blog doesn’t come up at the top of the Google search when  you search for the various related keywords.  [That’s one reason I’m writing this blog post, to boost up that one.]

The solution is related to your Active-X control settings.  BAM uses a lot of Windows and Active-X plugins to accomplish what it does inside your Excel spreadsheet.   You have to go to “File” then “Options”, select Trust Center – Trust Center Settings – ActiveX Settings. Set ActiveX Settings to anything except “Disable..” as shown on the blog referenced above.

Every time you open a new spreadsheet (to create a new BAM Activity View), you will have to do this step again.  But if you save a spreadsheet and then re-open, those settings will be saved in that spreadsheet.

After doing the above, you may get this next error:

Unable to get resource string

The solution I chose was to install the “Office Multilingual User Interface (MUI) pack”.

This video gives an idea of some of the projects a BizTalk Architect are involved with. Neal Walters has worked in a number of industries, including: Oil and Gas, Mortgage, Banking, Manufacturing and Reverse Logistics, Government, Healthcare, Insurance, Aviation/Airline Fueling, Student Loans and Non-Profits.

Neal started with mainframes back in 1981 with Amerada Hess Corporation in Tulsa, Oklahoma. Tulsa used to be called “the oil capital of the world.” It was actually in Tulsa where Neal taught his first BizTalk class on the 2002 version (which is horrible compared to BizTalk 2004 and onward).

Neal was an independent Microsoft Certified Trainer from 2001-2004. After his last mainframe project, which was a Y2K conversion as an IDMS database administrator, he re-tooled himself in the Microsoft platform. Back then, this meant Visual Basic; but as soon as .NET and C# came out, Neal got trained in that, and became a trainer on it as well.

He was chosen to attend the BizTalk 2004 Trainer the Trainer class in Redmond, Washington. This was an intense one week bootcamp. Microsoft was interesting in pushing BizTalk and increasing sales, so they knew they would need trainers and people in the field with knowledge on the product. After that class, he taught the BizTalk 2004 class over 15 times; it was one of his most popular classes.

As a trainer, Neal got into a variety of topics. Being independent, one has sometimes has to learn the product and then teach it. He taught VB, C#, classic ASP, and even JavaScript and PHP. A Microsoft XML course was also one of his popular classes. The funniest thing that once happened was a Java based company brought him in to teach Microsoft XML, when in fact they want to learn how to do XML in Java. It wasn’t funny at that time!

Neal decided to go back into consulting, having been a consultant since 1994 in the mainframe world. He first BizTalk project was for a E-Commerce company near Ft. Lauderdale Florida that sold watches and diamonds and other luxury goods on three of their own websites. They did an integration with Amazon that put the Florida company’s products on Amazon, and processed the orders into their existing back-end system.

His next project was in Colorado Springs for a non-profit Christian organization that allows individuals to sponsor children in countries around the world. Neal sponsored a child with that company, and has been doing it since then. He said it’s great to watch his sponsored child grow up, and knows the benefits of the program. This organization received orders from the web, then sent them through a backend Service Oriented Architecture consisting of webservices and BizTalk.

From there he actually had two separate contracts in Santa Fe, New Mexico. The first was with the State of New Mexico doing Medicaid. That involved using HIPAA messages such as the 837 for doctors, dentists, hospitals, and pharmacies. The claims were then sent on to an existing mainframe system to be adjudicated for payment. Next, he was brought in to a mortgage company that was doing an complete rewrite of their entire software system, and BizTalk was the hub of their Enterprise Application Integration, which integrated 17 disparate systems.

Next, he had a short project in Waco for an insurance company. What was interesting about this project was they wanted to have a mainframe COBOL/CICS screen make a real-time call to an external company’s web service. This was done by using Microsoft’s Host Integration Server. It used to be a separately bundled product, but now it is included in BizTalk.

From there, Neal landed another great location – San Francisco, California in the heart of the financial district. A bank wanted to integrate with a third party “wealth management system”. It turns out that many Hollywood stars and wealthy people in California hire “wealth managers”, who might do everything from hire their maid staff, find them a chauffeur, and even manage their money for them. They wanted to be able to move money between accounts using their system, without having to logon to the bank’s website. Another big feature was the ability to do wire tranfers from their system.

Then zoom across to the East coast in Boston, where Neal spent one of the good winters working with student loans again. Another non-profit was revamping their business model after changes made by the Obama adminstration. Data files came in from 100s of different colleges across the United States; and it turns out that many of those colleges have tiny IT departments that send a lot of “dirty data”. We were constantly struggling with cleaning-up the data they were sending in, mostly in CSV (Comma Separate Value) files.

Finally, Neal got his first BizTalk contract in his own home town of Dallas. A small company made a handeheld device that fuelers use to fuel airlines. The device collects the data of the fuel put on the plane for later billing purposes, and could actually shut off the pump to prevent overfueling. This was interesting because the BizTalk team there has to process large feeds from major airlines, for every flight at the airports we covered. So there was a lot of data filtering, and over a million messages being processed per day. As you know, flights change often. Then the fuel loads can change as well. The data was received using MQSeries, which was a great companion tool to use with BizTalk. Nightly summary files were also sent to dozens to fuelding companies at dozens of local airports.

Then, a second position in Dallas, Neal worked with an international company that repairs phones, tablet, laptops, PCs, and other electronic devices. The data load there was also astounding, as well as the number of customer/trading partners, many of which were insurance companies. When a person breaks a device, the Dallas office would repair it and send it back to the customer.

Neal is reachable at 214-455-8060 to discuss BizTalk consulting. You might have to leave a message, but he will get back to you (especially if you mention that you are NOT a recruiter). Neal has realized with the internet, there is no need need for the middle man. He is incorporated, works C2C (Corp-to-Corp), and has met all the liability and workers compensation requirements.

Have you ever been confused by the long XPaths found in BizTalk? XPath is a great syntax used to select or query XML and return XML nodes or specific element/attribute values. XPath is good to know for BizTalk, .NET programming, and XSLT.

Example 1

For example, look at the schema below.

Demo BizTalk Schema

Demo BizTalk Schema

If you click on the “Name” element, then go to the properties window, you will see the following Xpath in the value of the field called “Property XPath”.

/*[local-name()='SchemaDemo2' and namespace-uri()='http://BizTalkSampleProject.SchemaDemo2']/*[local-name()='ShippingAddress' and namespace-uri()='']/*[local-name()='Name' and namespace-uri()='']

What does the above actually mean? W3Schools has a good tutorial on XPath, but it doesn’t cover dealing with namespaces. On this xpath syntax page, it does however define a predicate which is the XPath code in between the square brackes [].

Predicates are used to find a specific node or a node that contains a specific value.

Predicates are always embedded in square brackets.

In the table below we have listed some path expressions with predicates and the result of the expressions:

 

And here are the example they give, but no examples with namespaces!

Xpath_Predicate_Examples

So think of a predicate as a “Where Clause”. Even in SQL terminology, a predicate is something that results in a TRUE or FALSE.

So when you see the typical Microsoft generated XPath, it can be explained as follows. The / is the normal separator, and starting point from the current node. The “*” is the abbreviation for the “child” axis (explained more here: https://www.stylusstudio.com/docs/v2007/d_xpath95.html, http://www.informit.com/articles/article.aspx?p=102644, http://www.w3schools.com/xsl/xpath_axes.asp). It selects all element children of the context node.

But then we don’t really want all the nodes, so we apply the predicate or “where clause” – only give me the nodes where the local-name()=’SchemaDemo2′ and namespace-uri()=’http://BizTalkSampleProject.SchemaDemo2′ (and that must be enclosed in brackets). local-name() and namespace-uri() are functions (or internal variables?) that return the current element name and the current namespace). If you have the same namespace all the way through your schema, then you can leave off the namespace-uri() predicate.

In other words, the longer full syntax is often shortened to this:

/*[local-name()='SchemaDemo2']/*[local-name()='ShippingAddress']/*[local-name()='Name']

The only time this shortening would get you in trouble is if you had the same element under more than one namespace.

Example 2

Consider this example, where I created an Address schema, then I imported it into the Labels Schema, then made one Billing Address and one Shipping Address.

Nested_Schema

/*[local-name()='Labels' and namespace-uri()='http://BizTalkSampleProject.Labels']/*[local-name()='BillingAddress' and namespace-uri()='']/*[local-name()='Address' and namespace-uri()='http://BizTalkSampleProject.AddressSchema']/*[local-name()='Name' and namespace-uri()='']

It turns out that even in this case, you could shorten the XPath to the following:

/*[local-name()='Labels']/*[local-name()='BillingAddress']/*[local-name()='Address']/*[local-name()='Name']

So one case where you couldn’t shorten it would be when you had two Address nodes with different namespaces.

Testing XPath Online

Now, I want to go into some reasons why I wrote this blog. I was using C# to loop through XML of a bigger document.
Inside the loop, I have this chunk of XML in an XmlNode.

I’ve been testing XPath Online with http://www.xpathtester.com/xpath.
If I start my XPath with two slashes, it seems to be going back to the top of the entire XML document, rather than just searching the current node.

I originally had this xpath:

//*[local-name()='Property'][@Name='Name']

So basically, I my aim is to pick out the orchestration variable name, which is the example below is “strYNTraceOnFromSSO”;
It works fine in Xpath Tester because I have just the above text, but in C# it is returning some node outside of that text. (I guess I could reload the XML in a new XML document, but using this as an opportunity to get a better graso my XPath).

This was my XML, which actually came out of an .odx file.


Here is what the query looks like in XPath Tester: Xpath Tester So if I start my Xpath with /*, I would need to specify the parent node, right? This example returns the following error:

    /*[local-name()='Element']/[local-name()='Property'][@Name='Name']

returns error: ERROR – Failed to evaluate XPath expression: javax.xml.transform.TransformerException: A location step was expected following the ‘/’ or ‘//’ token.

Looking back, it’s an obvious error, but it wasn’t at the time. In easy XPath, you can just put a slash between the elements. But when using the predicates, you have to put /* to indicate “all elements where the name=’Property'”.

Why is there no slash or /* before the attribute? Because it’s a predicate on the predicate. Only return for me Property elements that have an attribute called “Name” that also has a value of “Name”.

Here is the correct result in XPath Tester, returning the selected Property element:
XPathTester_Example2

C# Code

Above example found the desired Property element only. In the sample code below I go down and pick off the value of the attribute.  This code basically loops through all the variables in an orchestration.

            string xpathTopLevelVariables = @"//*[local-name()='Element'][@Type='VariableDeclaration']";
            XmlNodeList xmlNodeList = xmlNodeServiceDeclaration.SelectNodes(xpathTopLevelVariables);

            foreach (XmlNode xmlNode in xmlNodeList)
            {
                XmlDocument xmlDoc2 = new XmlDocument();
                xmlDoc2.LoadXml(xmlNode.OuterXml);   // this seems unnecessary to me, but it works 
                string xpathProperty = "/*[local-name()='Element']/*[local-name()='Property'][@Name='Name']/@Value";
                string variableName = xmlDoc2.SelectSingleNode(xpathProperty).Value;
                // other unrelated code omitted 
           }

 


Note: For some reason, I had to reload the chunk of XML into a new xml document, otherwise I was getting all data from the top rather from the current node downward. I wanted to put the SelectSingleNode method directly on the xmlNode variable, but I didn’t solve that challenge.

Sample of error:
Error_DoesNotBelongToTheSameApplicationOrReferences

This was happening when I was exporting and importing bindings from a developer machine to our QA (Quality Assurance) environment. Looking back, the problem seems obvious, but it I lost over an hour chasing it down.

So on my developer machine, I had a project reference from the application project to the “BizTalk EDI Application”. On the QA system, I had the same target application, but at that point, it had never used EDI, so it didn’t have the project reference. All I had to do was add the project reference, then the import worked fine.

BizTalk Core Databases

BizTalkMgmtDB Most entities from BizTalk Admin Console are stored in this database (applications, orchestrations, assemblies, sendports, receive ports and locations, etc…)Most updates in BizTalk Admin Console are stored in this database (applications, orchestrations, assemblies, sendports, receive ports and locations, etc…). Thus BizTalk Admin Console is basically an update program to this database. You can also use WMI to update it.
BizTalkMsgboxDB This is the MessageBox containing information about the messages, instances and subscriptions which are processed by BizTalk. Can be large and has a lot of heavy processing. A large site can have more than one msgbox (see “Scaling Out”)
BizTalkDTADB Contains information about all the processed messages and instances
Archive/Purging
BizTalkRuleEngineDB database for the Business Rules Engine, updated by the GUI “Business Rule Composer” program. Use the “Business Rules Engine Deployment Wizard” to migrate business rules from one environment to another.
SSODB Single Sign-On Database – BizTalk stores data related to SendPorts and Receive Ports here. For sure, any password that needs encrypted goes here (for example an FTP password). BizTalk will not function at all if this database is not available and the corresponding service (“Enterprise Single Sign-On Services”) is not running. This service must come up before BizTalk.

BizTalk installs creates a SQL Agent Backup job, but it will be disabled, and you have to configure it. See this article on Backing Up and Restoring Biztalk Server Databases.

BizTalk BAM (Business Activity Monitoring) Databases

Obviously, these are used only if you have implemented BAM. I’d love to know what percentage of BizTalk shops use BAM. Most places I’ve worked don’t use it. See BAM Quick Start

BAMStarSchema Contains the staging table, and the measure and dimension tables which are set in originally in an Excel spreadsheet, then later deployed to this database.
BAMPrimaryImport Contains raw tracking data. For example, when you capture data from an orchestration, it is originally stored here, before being processed further. If you need to right custom queries against this database, be sure and use the views, not the underlying tables.
BAMArchive Archive of old business activity data. This keeps the BAM Primary Import database smaller by migrating older data here.
BAMAnalysis Contains the OLAP (three dimensional) data cubes. Learn more about OLAP here.
BAMAlertsApplication Contains alert information for BAM notifications. The web application, called the “BAM portal”, allows users to specify conditions and events on which they want notifications and alerts to occur.
BAMAlertsNSMain Contains instance information specifying how the notification services connect to the system that BAM is monitoring.

Do you need to “take inventory” of your BizTalk artifacts (assets)? I.e. Get a simple count or high-level summary for your BizTalk Artifacts?

For years, we’ve had tools like BizTalk Documenter (free on CodePlex), but that just works for one application. What if you walk into a client site and you want to know in high-level, executive summary terms, what is the size of the application?

The code below is a quick SQL-only solution. Some people shutter at the idea of using SQL against the BizTalk databases, but it’s fast and easy. Similar code could be written in C#, Powershell or other tools using ObjectExplorer, WMI, or Powershell plug-ins for BizTalk.

use BizTalkMgmtDB
set transaction isolation level read uncommitted  --allows dirt reads but avoids locking 
SELECT 
    APP.nvcName as AppName,
	(Select count(*) from bts_receiveport AS RP 
					where RP.nApplicationID = APP.nID 
					) as RcvPortCount,
	(Select count(*) from adm_ReceiveLocation AS RL 
				     INNER JOIN bts_receiveport AS RP2 ON RL.ReceivePortId = RP2.nID 
					 where RP2.nApplicationID = APP.nID 
					 ) as RcvLocCount,
	(Select count(*) from bts_Orchestration AS ORCH 
				     INNER JOIN bts_assembly AS ASSEM ON ASSEM.nID = ORCH.nAssemblyID
					where ASSEM.nApplicationID = APP.nID 
					) as OrchCount,
	(Select count(*) from bts_Pipeline AS PIPE 
				     INNER JOIN bts_assembly AS ASSEM ON ASSEM.nID = PIPE.nAssemblyID
					where ASSEM.nApplicationID = APP.nID 
					) as PipelineCount,
	(Select count(*) from bt_documentSpec as BTSCHEMA 
				     INNER JOIN bts_assembly AS ASSEM ON ASSEM.nID = BTSCHEMA.AssemblyID
					where ASSEM.nApplicationID = APP.nID 
					) as SchemaCount,
	(Select count(*) from bt_mapSpec as MAP
				     INNER JOIN bts_assembly AS ASSEM ON ASSEM.nID = MAP.AssemblyID
					where ASSEM.nApplicationID = APP.nID 
					) as MapCount
	--(Select count(*) from bts_Component AS PIPECOMP
	--			     INNER JOIN bts_assembly AS ASSEM ON ASSEM.nID = PIPECOMP.nAssemblyID
	--				where ASSEM.nApplicationID = APP.nID 
	--				) as PipelineComponentCount

FROM bts_application AS APP 
where App.IsSystem = 0 and App.nvcName not in ('BizTalk EDI Application','BizTalk Global','rosettanet') 
order by App.nvcName

Example Results

Inventory_BizTalk_Artifacts

Ideas for Improvements

Run the same query on each of your environments: Dev, QA, Stage, Prod, etc… and store results in a SQL Holding Table. Then do a final query to merge the results and show which apps are in which environment, and identify when the number of artifacts is different. This would help you to compare one environment to another, for example Test to Prod.

I spent about three hours chasing down these errors

Three Different Errors in my IIS Log

When using Port:

80040213 The_transport_failed_to_connect_to_the_server.

8004020f [no text, just this code]

When using Pickup Directory”

80040222 The_pickup_directory_path_is_required_and_was_not_specified

So when reading other blogs, they often tell you to change from Pickup Directory to Port or vise versa.

Context

This code below came from an old Windows 2003 machine running IIS.  I’m migrating to an Amazon EC2 instance running Windows 2012.  My application is a classic ASP application that I have never rewritten.  I remember having similar difficulties back then, decided Microsoft’s SMTP was too complicated, tried some others, and ended up running MailEnable.

CODE

I have a VBScript Class in a separate file that can be shared by any program that needs to send an email.  That’s the first block of code below.  The second block of is uses the class to try to send the email.  It was bombing on the line that said “cdomsg.Send”.

<%

Class Mail
public emailTo
public emailFrom
public emailSubject
public emailBody

public function send

Set cdomsg = CreateObject(“CDO.Message”)

Set iConf = Server.CreateObject(“CDO.Configuration”)

Set Flds = iConf.Fields

‘ Neal changed 12/12/2007 – was getting error:
‘ Arguments are of the wrong type, are out of acceptable range,
‘ or are in conflict with one another
‘ Actually – had to add the MetaData clause at the top
Flds.Item(cdoSendUsingMethod) = 1 ‘ 1=pickup 2=port
‘ The value of 2 might require a user/pass, but you must specify port & server
‘Flds.Item(“http://schemas.microsoft.com/cdo/configuration/sendusername”) = “MyUser”
‘Flds.Item(“http://schemas.microsoft.com/cdo/configuration/sendusername”) = “MyPass”
Flds.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserverpickupdirectory”) = “c:\inetpub\mailroot\pickup”

‘Flds.Item(cdoSMTPServer) = “127.0.0.1”
Flds.Item(cdoSMTPServer) = “localhost”
Flds.Item(cdoSMTPServerPort) = 25
Flds.Item(cdoSMTPconnectiontimeout) = 10
Flds.Update

Set cdomsg.Configuration = iConf

cdomsg.from = emailFrom
cdomsg.to = emailTo
cdomsg.subject = emailSubject
cdomsg.textbody = emailBody
cdomsg.Send
Set cdomsg = Nothing ‘ not allowed to reuse it for another message
end function

End Class
%>


<%
<!--METADATA TYPE="typelib" UUID="CD000000-8B95-11D1-82DB-00C04FB1625D" NAME="CDO for Windows 2000 Library"-->
Response.Write "Attempt to send mail "
Set objMail = new Mail
objMail.emailFrom = "me@mydomain.com"
objMail.emailTo = "you@mydomain.com"
objMail.emailSubject = "TestMail.asp from mydomain.com server"
objMail.emailBody = "Body of email, this is a test"
objMail.Send  ' was getting error on this line when attempting to send email
Set objMail = Nothing ' not allowed to reuse it for another message
Response.Write "Mail sent"
%>

The Steps I Took To Solve the Problem

Obviously, I had to get IIS installed.  One possible issue is that I installed it, but I didn’t configure it.

1. I basically followed these steps to Setup and Configure SMTP.   I did not setup their firewall rules, because I’m running IIS and SMTP on the very same server. I did one thing not on their list. From the “Security” tab of the SMTP Virtual Server Properties, I added the local user account tied to the Application Pool.

2. I did add a local user account, assigned that account to my IIS Application Pool, restart the app pool.  I gave that account access to my IIS files directory, and to the pickup directory.  At this point, I’m not sure that was needed or not.

3. I never got to work using 2=port, only 1=pickup.

Flds.Item(cdoSendUsingMethod) = 1 ‘ 1=pickup 2=port

If you specify 1 for pickup, then I now am pretty certain you must also specify the pickup directory name:

Flds.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserverpickupdirectory”) = “c:\inetpub\mailroot\pickup”

I’m guessing that CDONTs writes to that directory, and that write access is required for the user running the App Pool.

Not having the “pickup directory” specified was causing this error:  80040222 The_pickup_directory_path_is_required_and_was_not_specified

Followup: Later I plan to try one of my other IIS websites, to see if they work “as is”, or if they need a local account and security granted as well.

The error “Errors exist for one or more children” can often be tricky to uncover.

Usually, when I get this error, I double click on it, and an orchestration shape will be slightly highlighted or bolder than normal, and it will have a red exclamation point on it. You then open it, look for a tiny red squiggly mark, and try to guess what the syntax error is.

Today, I got this error, but no orchestration shape was highlighted. I was about to pull my hair out, then luckily, out of the corner of my eye, I saw a red exclamation mark in the Orchestration Viewer next to a Multipart Message. Apparently I had added a new one, and either 1) not connected it to any schemas, or 2) not deleted it.

Moral of the story – look in the Orchestration Viewer, not just the orchestration shapes for the error.

ErrorsExistInOneOrMoreChildren_OrchestrationView

I think most blogs talk about requiring that the PortType be “Public” instead of “Internal”.  However, today I learned that not only do you have to build (compile) the orchestration, you must also deploy it.  Even though I compiled, the new port was not appearing in the WCF Publishing Wizard.  I was getting desparate, finally decided to do a Deploy, then it worked fine.