In Microsoft SQL, how can we group by a date, when the column we want to group by is a date time?
For example, you want the number of rows created for each date, and you have a column in the database called Date_Added (or in my example below, TrcDateTime).

Code Sample 1:

select DATEADD(dd, 0, DATEDIFF(dd, 0, trcDateTime)) as Date, 
       count(*) as count 
from trace 
GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, trcDateTime))

Output 1:

The above gives the correct counts, but gives a rather  ugly Date, because it shows the time as 00:00:00.00.0000. To remove that, we can convert the date to a string.

Code Sample 1:

select LEFT(CONVERT(VARCHAR, DATEADD(dd, 0, DATEDIFF(dd, 0, trcDateTime)), 120), 10)  as Date, 
       count(*) as count
from trace
GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, trcDateTime))

Output 2:

This is how you can convert a DateTime to a String:

LEFT(CONVERT(VARCHAR, trcDateTime, 120), 10)

I took the entire expression from the first query: DATEADD(dd, 0, DATEDIFF(dd, 0, trcDateTime))
and substituted it where the italic trcDateTime was in the expression above.

 

 

 

It’s long been said that a picture is worth a thousand words.  I think it’s necessary to have a few diagrams to show the BizTalk flow of any application. I’m not happy with UML, because they don’t show the many aspects of BizTalk.  I’ve walked into companies before that had no diagram, and the first thing I did was to create one.  In the process, I learned the flow of the system, and I document it for myself and others.  I often pin such diagrams to my cubicle or tape them to my office wall, and refer to them frequently.  They are constantly used in meetings to explain steps and processes to managers, users/customers, and/or other developers.

Ideally, the diagram should include:
1) Some key filenames (or at least partial filenames, as the paths are going to be different from development to QA to Production) 2) Key programs (orchestrations and any C# programs)
3) BizTalk Send/Receives
4) Any touchpoints with existing in-house systems or external trading partners (vendors, customers, etc…)

Here are some of the shapes and colors I’ve been using as part of standard Visio:
1) Light Yellow Parallelogram – Receive or Send Port – If relevant, I often include a map name or pipeline on the shape.
2) Blue Rectangle – Orchestration
3) Disk – Disk
4) Database – Database or Tables in Database
5) Cloud – Internet (FTP or HTTP access to outside company)
6) Red Rectangle – Pollers (C# programs that read database and create XML files)

NOTE: There is a package of Visio 2013 Stencils for BizTalk (by Sandro Pereira).  I have not yet used them, but they relate to representing Biztalk Server physical architectures, integration architectures and solution diagrams.

I’ve also used http://Draw.IO, as sometimes, not everyone has access to a license of Visio.

The following is an example from a larger project where we were processing a variety of Rosetta Net documents. The diagram also includes the project management number for each task (this company was using a system called “Redmine“).  In the example below, I wanted to emphasize some of the logic of the orchestration, and I had room, since I had was putting only one or two Rosetta documents per page. I also included the name of the developer working on in the header (removed from diagram below), as each developer was working on maybe 2-3 Rosetta documents.

Here are some of the shapes and colors I’ve been using as part of standard Visio:
1) Light Yellow Parallelogram – Receive or Send Port – If relevant, I often include a map name or pipeline on the shape.
2) Large Green Portrait Rectangle – New Development Orchestration for this application [all it’s main logic is to the right]
3) Green Landscape Rectangle, Diamonds, etc… – Steps in the orchestration
3) Cylinder – Disk and DB
4) Blue Rectangle – This was an existing orchestration that we share across applications
5) Purplse Hexagram – Pollers (C# programs that read database and create XML files)

 

There is also a free tool called Biztalk Documenter. It’s project description says:

This tool creates documentation for a given  BizTalk server 2006, 2006R2 2009, 2010, 2013, 2013R2 or 2016 TAP installation. This project has used the source code of the original BizTalk 2006 Documenter , updated and improved it. We have also added the SSO documentation by the Red Eyed Monster from the BizTalk 2010 documenter

The related bog is “Documenting Your BizTalk Solutions“.  That blog has a full set of screen shots to show you what comes out of this tool.

When I’ve had one or more long complicated orchestrations, that need extra documentation, I have used TechSmith’s SnagIt to take screen shots, and paste into a Word document.  Then I write paragraphs of text in the document describing what needs further clarification.  Unfortunately, BizTalk Orchestrations don’t have a “text” or “doc” shape.  I often use “Group” shapes in BizTalk Orchestrations to provide additional documentation “inline”, and I even nest them if I need more text than will fit.  I also put long text on the Decide shapes if that is helpful.  Here’s an example:

 

There are advantages and disadvantages to both manual and automated documentation.  Obviously, the automated documentation can be updated any time by just re-running it. But it is usually bulky and sometimes too detailed, and much too large to print.  I’ve always preferred or one or twp page diagram that summarizes the whole system with “the big picture”.  As of yet, I think only a human can do this.  Part of the art is when to abstract – i.e. what to include in the diagram, and what to leave out.  If you try to include too much, you will end up with 3 to 5 pages of diagrams and then you can get lost in the detail.  If you omit too much, then the diagram may be of little value.   It’s even possible that you might want both, a very high level diagram for management, and then a more detailed diagram for architects and developers.

 

Neal Walters has been work exclusively with BizTalk since the 2002 release, and has both been a Microsoft Trainer of BizTalk and a consultant since 2004.  He is now developing a Dallas SEO agency (Search Engine Marketing).

 

One way to check for missing data is to do an xpath count.

The two-step approach (put the xpath in a variable first). Some might find this code cleaner, but you use a variable. Note that you have to use the conversion statement to convert the string result of the xpath to a number, if you need to test that number as I do in the following “if” statement.

 

strCountMessageKeysXPath = "count(//*[local-name()='MessageKey']";

intCountMessageKeys =
System.Convert.ToInt32(
xpath(msgCreateSPServiceOrderAckResp.parameters,strCountMessageKeysXPath)
);

// Yes you can put if statement in expression shapes (but not message assignment shapes) 
if (intCountMessageKeys >= 2) 
{
   strXPath = "//*[local-name()='MessageKey'][2]"; 
   strWebRespMessage2Key = xpath(msgCreateSPServiceOrderAckResp.parameters,"string(" + strXPath + ")"); 
}

 

The one-step approach:

 

strCountMessageKeysXPath = ;

intCountMessageKeys =
System.Convert.ToInt32(
xpath(msgCreateSPServiceOrderAckResp.parameters,"count(//*[local-name()='MessageKey']")
);
// Yes you can put if statement in expression shapes (but not message assignment shapes) 
if (intCountMessageKeys >= 2) 
{
   strXPath = "//*[local-name()='MessageKey'][2]"; 
   strWebRespMessage2Key = xpath(msgCreateSPServiceOrderAckResp.parameters,"string(" + strXPath + ")"); 
}

 

 

NOTE: The reason I have .parameters after the message is that it is a multi-part message.
If you don’t get that correct, you may get the error “The expression you have entered is not valid”, or “An xpath expression must be of the form” if you hover over the error.

 

If you get the error “An xpath expression must be of the form …” (in an expression shape of a BizTalk orchestration), here’s one possible solution.

I was using a multi-part message, so instead of msgCreateSPServiceOrderAckResp, I had had to specify msgCreateSPServiceOrderAckResp.parameters.

The compiler just shows the error “The expression that you have entered is not valid”. But when you find the small red mark, and mouse over it, you see the following:

Here is the corrected version:

 

 

 

In a previous article, we discussed how to use a Powershell job to delete files over a certain age.

But what if if you want to do it using straight “pure” DOS .bat or .cmd file?  The following shows you how it’s done:

REM Cleanup all files more than 7 days old
e:
cd E:\BizTalk\App\Backup
forfiles /S /M *.* /D -7 /C "cmd /c del @path"

NOTE however, UNC Paths are not supported. You CANNOT even specify UNC name to a remote server in the PATH parameter.

REM Cleanup all files more than 14 days old
forfiles /P \\server\BizTalk\App|Backup /S /M *.* /D -14 /C "cmd /c del @path"

The “forfiles” command is documented here, and
briefly recapped below:
.

It selects and executes any command on a file or set of files.

 

  • With forfiles, you can run a command on or pass arguments to multiple files. For example, you could run the type command on all files in a tree with the .txt file name extension. Or you could execute every batch file (*.bat) on drive C, with the file name “Myinput.txt” as the first argument.
  • With forfiles, you can do any of the following:
    • Select files by an absolute date or a relative date by using the /d parameter.
    • Build an archive tree of files by using variables such as @FSIZE and @FDATE.
    • Differentiate files from directories by using the @ISDIR variable.
    • Include special characters in the command line by using the hexadecimal code for the character, in 0xHH format (for example, 0x09 for a tab).

 

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.

I have a C# trace routine, that writes data to a SQL Trace table. It’s kind of like my own Log4Net, and allows a unified trace between BizTalk, WebServices, and other types of progams.

I had the interesting task the last month of writing a WCF Custom Behavior to make JSON work in BizTalk 2010. Moving to 2013 or 2016 was simply not possible at this client in time for implementing the project.

So I had lots of Trace statements in the custom behavior. The bizarre thing was that when I call the trading partner’s webservice, and it succeeded, then I could see the rows inserted. In Debug/Attach mode I could see them as well. But if the trading partners’ webservice returned any error, all the rows inserted were being rolled-backed (rollback). And of course, the Trace results are much more needed in the event of an error; for example, I want to see the JSON that was created.

I earlier had decided to write the JSON to a disk directory. But I still prefer having it in the Trace as well. The Trace has an column of XML data type, where I can store text and blobs. (If the data is not XML, the C# trace program just puts a dummy xml <wrapper> tag around it. Normally in this column I store the request and response to and from any web service. It makes debugging very fast and easy.

I started researching about distributing transactions, and fortunately found a quick and easy fix. All I had to do was add the “Enlist=false” statement to the connection string:

   <connectionStrings> 
       <add name="Trace" connectionString="server=localhost;database=Trace;Integrated Security=true;Enlist=false" />
   </connectionStrings>

I found the answer on this page about .NET distribution transactions. At first I thought I might have to change my C# code to use “EnlistDistributedTransaction” method, but that wasn’t necessary. Just changing the connection string as shown above worked.

I have a “Website” in IIS. It had a virtual directory with SSL working. I added a second virtual directory for another external client.

I’m using SOAP-UI to test. It works fine with normal http, but gives an http status of 404 when I use https.

Example URL:
https://wstest.mydomain.com/App1/myService.svc
https://wstest.mydomain.com/App2/myNewService.svc

I’ve looked at the IIS logs, nothing exciting to report from there.
(This is on Win 2008/R2.)

It turns out that the web.config of the one not working had:

          <security mode="None" />


and I simply changed it to:

          <security mode="Transport" />

Issue/Scenario:

I have a new application that I’m building. I have for example three projects, App.Common, App.In, and App.Out.

We have a somewhat debatable policy of keeping one app separate from the others and almost no application cross-references (still on BizTalk 2010 – but in 2013 this is supposed alleviated, partially or in whole).

I had a web service schema copied in to App.Out. And I developed the proof of concept in App.Out.

Now, it turns out we have about 6 output messages.  There was a also some debate as to whether they should go in the single AppOut project, or in six different projects (App.Out1, App.Out2, etc…).  The architect wanted the second approach, so different developers could be assigned to each one in order to possible meet a tight development deadline.

Now, I don’t want that same web service schema to be deploy 6 times in the same application. So I moved the schemas to the App.Common.  And of course the map doesn’t compile because I change the location of the schemas.

Solution

I was afraid I was going to have to do map surgery, but it turns out I could just re-reference the schema in the map.  Since the schema and nodes are the same, none of the mapping was lost.

Internals of the Map

I was afraid I was going to have to do map surgery, but it turns out I could just re-reference the schema in the map.  Since the schema and nodes are the same, none of the mapping was lost.

But for fun, if I had to do map surgery, this is chat change. The Original “Before” is on top, and the “After” is on the bottom”.

NOTE: When you open a map there are no line breaks.  I use the XML tools in NotePad++ to format the XML (but don’t save it after formatting, always do the formatting on a copy).

You can see below how the “Reference Location” attribute changed.
I’m mapping from our Canonical schema to an internal web service schema.

 

<SrcTree RootNode_Name="Canonical_DATA">
<Reference Location="CanonicalArtifacts.Schemas.MY_CANONICAL_SCHEMA_V4" />
</SrcTree>
<TrgTree RootNode_Name="OrderUpdateHeaderByMessageID_New">
<Reference Location="SchemasWebSvc\B2BData_tempuri_org.xsd" />
</TrgTree>

<SrcTree RootNode_Name="Canonical_DATA">
<Reference Location="CanonicalArtifacts.Schemas.MY_CANONICAL_SCHEMA_V4" />
</SrcTree>
<TrgTree RootNode_Name="OrderUpdateHeaderByMessageID_New">
<Reference Location="AppOut.SchemasWebSvc.B2BData_tempuri_org" />
</TrgTree>

 

First download the C+ distributable from Microsoft: Download 64

Download the CURL MSI from here:
Download CURL MSI

Install will default to this directory, and here is the curl.exe: “c:\Program Files\cURL\bin\curl.exe”.

You need to find the MSI. Many downloads of CURL will include the source code and it requires you to build it with C++ compilers and such. That’s more trouble than you usually need to go through when you just want to install and use the tool. Fortunately, the anonymous author at “ConfusedByCode.com” has been nice enough to create the MSI (Microsoft System Installer) files for CURL so that you can download it, install it, and be using it within minutes.

What is CURL?

A command line tool for getting or sending files using URL syntax.

How is CURL applicable to BizTalk or B2B Teams

B2B and BizTalk developers can use CURL to test websites, often of their trading partners. CURL can be run as part of a batch (.bat) or command (.cmd) file if you need to automate a test or schedule a download using Windows Task Scheduler.

Example Curl commands

Download the HTML of a page to your disk:

curl -o example.html www.example.com