You might open a file like this:

ISA*00* *00* *ZZ*123000013 *ZZ*ABC3001 *160719*1600*U*00401*201160030*0*P*;~

and then want to know what is company with ID=123000013? I don’t know of any any way in the BizTalk Admin console to do a reverse lookup like this.


select 
       P.PartnerId as 'ParterID', 
       P.Name as 'PartyName', 
       BP.Name as 'ProfileName', 
	   BP.ProfileID,
	   BI.Qualifier, 
	   BI.Description, 
	   BI.Value, 
       P2.PartnerId as 'PartnerID', 
	   P2.Name as 'OtherPartyName', 
	   BP2.Name as 'OtherProfileName' ,
	   BP2.ProfileID,
	   BI2.Qualifier, 
	   BI2.Description, 
	   BI2.Value, 
	   * 
from tpm.BusinessIdentity  BI 
inner join tpm.BusinessProfile BP   on BI.ProfileId = BP.ProfileId
inner join tpm.Partner P            on P.PartnerID = BP.PartnerID 
inner join tpm.Partnership ps       on P.PartnerID = ps.PartnerAId 
inner join tpm.Partner P2           on P2.PartnerID = ps.PartnerBId
inner join tpm.BusinessProfile BP2  on P2.PartnerID = BP2.PartnerID 
inner join tpm.BusinessIdentity BI2 on BI2.ProfileId = BP2.ProfileId
where BI.Value = '123000013' 
-- and BI2.Value = 'xxxxxxx'
order by P.Name, BP.Name 

We are using GitLab, an open source way to store source code on your own servers. I wanted to get all my branches into a clean directory, then recompile them to make sure everything had been checked-in properly. THe first step is to download all the branches into a directory.

NOTE: Apparently the –single-branch option did not exist in previous versions of Git.

d:
cd \GitRecompile
git clone --single-branch -b myBranchName http://git.Site.com/Site.BizTalk/Site.BizTalk.Application1
git clone --single-branch -b myBranchName http://git.Site.com/Site.BizTalk/Site.BizTalk.Application2 
git clone --single-branch -b myBranchName http://git.Site.com/Site.BizTalk/Site.BizTalk.Application3

This is an updated version of “InsertGenerator” from CodePlex. This tools works great when you want to create a few insert statements from a table, and save them in a file; or to transfer a few rows from one system to another. It’s great for lookup/code tables

I referred to this “how to build sql insert statements from a table” utility in an earlier blog.

This fix below is to allow support of a schema, because in some database you need to specify both the schema name and the tablename.

To run it:

exec InsertGenerator2 'YourSchema', 'YourTable'

Specify DBO for YourSchema if you that to use the default schema.

It will give you a list of insert statements that you can copy/paste to another system.

If your table has an identity column, add these statemetns before/after your inserts:

SET IDENTITY_INSERT YourSchema.YourTable ON
SET IDENTITY_INSERT YourSchema.YourTable OFF

USE [EchoOptimizer]
GO
/****** Object:  StoredProcedure [dbo].[InsertGenerator2]    Script Date: 7/11/2018 12:55:30 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[InsertGenerator2]
(@schemaName varchar(100), 
 @tableName varchar(100)) as

--Declare a cursor to retrieve column specific information for the specified table
DECLARE cursCol CURSOR FAST_FORWARD FOR 
SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName and TABLE_SCHEMA = @schemaName
OPEN cursCol
DECLARE @string nvarchar(3000) --for storing the first half of INSERT statement
DECLARE @stringData nvarchar(3000) --for storing the data (VALUES) related statement
DECLARE @dataType nvarchar(1000) --data types returned for respective columns
SET @string='INSERT '+ @schemaName + '.' + @tableName+'('
SET @stringData=''

DECLARE @colName nvarchar(50)

FETCH NEXT FROM cursCol INTO @colName,@dataType

IF @@fetch_status<>0
	begin
	print 'Table '+@tableName+' not found, processing skipped.'
	close curscol
	deallocate curscol
	return
END

WHILE @@FETCH_STATUS=0
BEGIN
IF @dataType in ('varchar','char','nchar','nvarchar')
BEGIN
	--SET @stringData=@stringData+'''''''''+isnull('+@colName+','''')+'''''',''+'
	SET @stringData=@stringData+''''+'''+isnull('''''+'''''+'+@colName+'+'''''+''''',''NULL'')+'',''+'
END
ELSE
if @dataType in ('text','ntext') --if the datatype is text or something else 
BEGIN
	SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'
END
ELSE
IF @dataType = 'money' --because money doesn't get converted from varchar implicitly
BEGIN
	SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'
END
ELSE 
IF @dataType='datetime'
BEGIN
	--SET @stringData=@stringData+'''convert(datetime,''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+''''''),''+'
	--SELECT 'INSERT Authorizations(StatusDate) VALUES('+'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations
	--SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'
	SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'
  --                             'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations
END
ELSE 
IF @dataType='image' 
BEGIN
	SET @stringData=@stringData+'''''''''+isnull(cast(convert(varbinary,'+@colName+') as varchar(6)),''0'')+'''''',''+'
END
ELSE --presuming the data type is int,bit,numeric,decimal 
BEGIN
	--SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+'
	--SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'
	SET @stringData=@stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'',''+'
END

SET @string=@string+@colName+','

FETCH NEXT FROM cursCol INTO @colName,@dataType
END
DECLARE @Query nvarchar(4000)

SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+ @schemaName +'.' + @tableName
print @query 
exec sp_executesql @query
--select @query

CLOSE cursCol
DEALLOCATE cursCol

Error that I had to solve today

The Messaging Engine failed while executing the inbound map for the message coming from source 
URL:"\\EDI1-FTP\Integrations\EDI1\ABCD\inbound\990\*.*" with the 
Message Type "http://MyComapny.Schemas.EDI.X12_00401.EDI990/v1.0#X12_00401_990". 
Details:"Loading property information list by namespace failed or property not found in the list. 
Verify that the schema is deployed properly. "

My Solution

I of course googled this, and there were various solutions, but none of them applied to me.

Here’s what happened where I work. A few weeks ago, some in our QA environment tried to move a schema or something from one Application to another. As that dangerous tool does, it will also bring along other things for the ride – if you are not very, very careful. He wasn’t! He asked our BizTalk Admin to fix things, and I was told it was fixed.

Then today, I got the above error. Took me almost all day to figure it out. I thought it was a problem with the EDI schema, as indicated; and redeployed it, cleaned-up the property schema, redeployed it again, but was still getting error.

I built a test receive/send port, and was able to figure out that I could drop a clean EDI XML file, or EDI formatted txt file, and it worked on the test port. So that seems to indicate no problem with promoting any fields in the EDI 990 schema.

So I kept racking my brain… What was different between the test receive/send and the real one. The only difference is that the real one used a map. So I temporarily removed the map from that receive port, and the message went on through to the next layer, and got an error there because of course it didn’t get mapped.

So with map it fails, without map it works! So I knew it was something to with the map. I checked the map for some basics, and it looked okay. Finally, I checked on the target schema. Turns out, it was one of the artifacts that got moved to the wrong BizTalk application. So the app I was testing had a reference to “Common.Schemas”; but the schema was not in “Misc.App”.

Unfortunately, I could use the “Move” artifact feature to just move the schema back. It again tried to gather too many other items to move. So I had to remove that schema, which involved 4 other apps that depended on it. I had to undeploy/redeploy them all! Then happy days!

Error

Got the following error when posting to a BizTalk receive location from SOAP-UI, but could have happened with any type of receive location:

HTTP/1.1 500 Internal Server Error
Content-Type: application/xml; charset=utf-8
Server: Microsoft-IIS/8.5
X-Powered-By: ASP.NET
Date: Thu, 05 Jul 2018 14:07:37 GMT
Content-Length: 2384

<Fault xmlns="http://schemas.microsoft.com/ws/2005/05/envelope/none"><Code><Value>Receiver</Value><Subcode>
<Value xmlns:a="http://schemas.microsoft.com/net/2005/12/windowscommunicationfoundation/dispatcher">
a:InternalServiceFault</Value></Subcode></Code><Reason><Text xml:lang="en-US">
There was a failure executing the receive pipeline: 
"MyPipeline, etc... Version=1.0.0.0, Culture=neutral, PublicKeyToken=1234123412341234"
 Source: "XML disassembler" Receive Port: "rpFrontEnd_TLRGLoadTenderOut_Web" 
 URI: "/TLRG/Shipment/DirectTender.svc" 
 Reason: Finding the document specification by message type 
 "http://myNamespace#MyRootNode" failed. 
 Verify the schema deployed properly.  </Text></Reason><Detail><ExceptionDetail 
 

Solution

This was a conversion from old 2010 system to 2013. On the first pass, I had forgotten a map that trimmed all the fields and removed empty nodes. The map used XSLT, and in the XSLT source, it had the wrong namespace from the old system.

Other Hints

Remember MessageType = NameSpace#Root

  1. Make sure the schema is really deployed (BizTalk Admin console)
  2. Check your spelling carefully, look for typos.
  3. If you have a map in the receive location, make sure the map is really running. I.e. does the messageType of the incoming message match the map. If the map doesn’t match, the incoming message will be passed on and could get this error.
  4. If you have a map, make sure the map has been tested and outputs the correct messageType.
  5. If you are doing JSON to xml (or any other file format to XML, make sure the pipeline is creating the correct messageType.
  6. Of course, make sure you restarted your Host Instances
  7. Check your values in BizTalk Admin before and after each deploy. If you are doing some kind of automated deploy, make sure your bindings for that deploy are current; they maybe resetting some value each time you deploy. In my case, I had a script to export the bindings, but I was actually running a similar script for another project. So everytime I reset the namespace value for the RootNodeNamespace in the JSON pipeline, it was getting reset on each re-deploy.

My disk folder structure is like this:

D:\Application\DEV
D:\Application\DEV\Cust1
D:\Application\DEV\Cust1\Archive
D:\Application\DEV\Cust1\Archive\210
D:\Application\DEV\Cust1\Archive\204
D:\Application\DEV\Cust1\Inbound
D:\Application\DEV\Cust1\Inbound\204
D:\Application\DEV\Cust1\Inbound\210
D:\Application\DEV\Cust1\Outbound
D:\Application\DEV\Cust1\Outbound\204
D:\Application\DEV\Cust1\Outbound\210
Similar for \Cust2
Similar for \Cust3 etc

I was looking for a sample 210 EDI Invoice to use in testing; and I wanted the most recent ones.

It turns out you can put a wildcard in the variable passed on the -Path parameter. In that case, I don’t really need the -Recurse option, but I included it anyway (just in case someone created additional subfolders).

cls 
$Startpath = "D:\Application\DEV\*\Outbound\210"

#If you just want THE Most Recent File: 
#$latest = Get-ChildItem -Recurse -Path $Startpath | Sort-Object LastAccessTime -Descending | Select-Object -First 1 
#Write-Host "Latest= $($lastest.Fullname)" 

#Most recent 12 files (or whatever number you chose in the -First ## parameter) 
$latestFiles = Get-ChildItem -Recurse -Path $Startpath | Sort-Object LastAccessTime -Descending | Select-Object -First 12
Write-Host "Outbound Latest 12 files" 
foreach ($file in $latestFiles) 
{
   Write-Host $file.FullName
}

Use the .FullName property to show the complete folder name in the results.

Any time you use the BizTalk WCF Service Publishing Wizard, it creates an IIS website with various content, including a “Temp” directory. The Temp directory has two entries:
1) BindingInfo.xml – can be used to create a Receive Port and Receive Location tied to your published web service
2) WcfServiceDescription.xml – this is the one we are going to discuss

I found this process described in this blog: Republishing BizTalk WCF Services. Also see How to Modify WCF Services previous published

You can run: BtsWcfServicePublishingWizard.exe /WcfServiceDescription=”your_WcfServiceDescription.xml”

The process is far from perfect. But it will restart the publishing wizard and on some of the screens, it will show you the same parameters. I noticed that the radio button on the screen that asks if your are publishing an orchestration or a schemas was not correct. And when you get to the screen that asks for the target namespace, the original value is not preserved and you have to re-paste it.

This could save you a lot of time, if you had a lot of services and operations in published together to the same website.

If you come to a site, and you find something has already been published, then you might have to add to it, or republish it. This can happen, for example, if you change the schema (add, removing, renaming elements and attributes).

<?xml version="1.0" encoding="utf-16"?>
<WcfServiceDescription xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Name="BizTalkWcfService" TargetNamespace="http://Sample.Integrations/" xmlns="http://schemas.microsoft.com/BizTalk/2006/01/Adapter/Wcf/Publishing">
  <LocationSettings Location="http://localhost:8081/Internal" Overwrite="true" AuthAnonymous="true" />
  <ApplicationSettings CreateReceiveLocations="false" ApplicationName="" />
  <AdapterSettings AdapterName="WCF-BasicHttp" />
  <MetadataSettings EnableMetadata="true" MetadataOnly="false" ReceiveLocationName="" />
  <WcfServices>
    <WcfService Name="Internal">
      <WcfOperations>
        <WcfOperation Name="Outbound210Service" Flow="RequestResponse">
          <WcfMessages>
            <WcfMessage Name="Request" Direction="Input">
              <WcfMessageType Category="XsdType" TypeName="Sample.FrontEnd.InvoiceOut.Schemas.Legacy_Outbound210_Canonical" AssemblyName="Sample.FrontEnd.InvoiceOut.Schemas, Version=1.0.0.0, Culture=neutral, PublicKeyToken=2393c33936991560" AssemblyLocation="C:\Windows\Microsoft.Net\assembly\GAC_MSIL\Sample.FrontEnd.InvoiceOut.Schemas\v4.0_1.0.0.0__2393c33936991560\Sample.FrontEnd.InvoiceOut.Schemas.dll" TargetNamespace="http://Sample.Integrations/" RootName="Outbound210" IsAnyType="false" IsEnvelope="false" />
            </WcfMessage>
          </WcfMessages>
        </WcfOperation>
      </WcfOperations>
    </WcfService>
  </WcfServices>
</WcfServiceDescription>

Whatever you specify for the WcfOperation, should have a corresponding service file. For example, Outbound210Service above, relates to OutboundService210.svc file. The WcfMessageType gives you the TypeName and AssemblyName of the schema that was published. The Targetnamespace attribute on the root element tells you the target namespace that was used.

To create a request-only (or one-way) webservice in BizTalk using the WCF-Service-Publishing-Wizard can be a little confusing at first.

After the first couple of screens, the wizard shows the screen below

You cannot delete the Response message. What you have to do is delete Operation1, then right click on the Service, select “Add Web method.”, then select “One-way” (instead of “Request-Response”, which was the original default.

Then you will have a one-way operation with only a Request method.

Other notes: Rename Service1 to some more appropriate name. It will create a file called xxxxxx.svc when the publisher has completed.
You can then add additional services if you want.

I’ve been doing a lot of “forensic” research on converting old systems lately. Sometimes I need to look at hundreds of ports at once, and opening each one separately to check the map is way to slow!

There are several joins required. The actual mapname is in the more generic bts_item table. You can get there by joining bt_MapSpec.

use BizTalkMgmtDb
select app.nvcName,
       sp.nvcName as SendPort, 
       item.Name as MapName,
       spt.nvcAddress, 
       bTwoWay, 
       sp.nvcSendPipelineData, 
       sp.nvcFilter
  from bts_sendport sp 
inner join bts_sendport_transport spt on spt.nSendPortID = sp.nID and spt.nTransportTypeId is not null 
inner join bts_sendport_transform spMap on spMap.nSendPortID = sp.nID 
inner join bt_MapSpec ms on ms.id = spmap.uidTransformGUID 
inner join bts_item item on ms.itemid = item.id 
inner join bts_application app on sp.nApplicationID = app.nID 
where sp.nvcName like '%210%'
order by app.nvcName, sp.nvcName 

select app.nvcName,
       rp.nvcName as ReceivePort, 
       item.Name as MapName,
       bTwoWay
from bts_receiveport rp 
inner join bts_receiveport_transform tr on tr.nReceivePortID = rp.nID 
inner join bt_MapSpec ms on ms.id = tr.uidTransformGUID 
inner join bts_item item on ms.itemid = item.id 
inner join bts_application app on rp.nApplicationID = app.nID 
where rp.nvcName like '%210%'
order by app.nvcName, rp.nvcName 

This program uses a mix of Object Explore Model (ExplorerOM) and BizTalk Provider for Powershell
to delete Send/ReceivePorts that match certain naming conventions.

We were refactoring an application and re-adding ports under a different application, so needed a script to delete the old pots.

If you run this script, I do of course suggest you run it once with the Delete Statements commented out!
Recode the selection criteria as needed for your purposes.

Parts of code from examples here: https://docs.microsoft.com/en-us/biztalk/core/receiveports-biztalk-server-sample

#===============================================================#
#=== ===#
#=== Delete the receive port named “My Receive Port” ===#
#=== ===#
#===============================================================#
Function DeleteSendPort ($sendPortName)
{
$receivePort = $catalog.ReceivePorts[$sendPortName]

if ($receivePort -ne $null)
{
$catalog.RemoveReceivePort($receivePort)

#=== Try to commit the changes made so far. If the commit fails, ===#
#=== roll back all changes in the trap handler. ===#
$catalog.SaveChanges()
}
}

Function DeleteReceivePort ($receivePortName)
{
$receivePort = $catalog.ReceivePorts[$receivePortName]

if ($receivePort -ne $null)
{
$catalog.RemoveReceivePort($receivePort)

#=== Try to commit the changes made so far. If the commit fails, ===#
#=== roll back all changes in the trap handler. ===#
$catalog.SaveChanges()
}
}

#===================#
#=== Main Script ===#
#===================#

#=== Make sure the ExplorerOM assembly is loaded ===#

[void] [System.reflection.Assembly]::LoadWithPartialName(“Microsoft.BizTalk.ExplorerOM”)

#=== Connect to the BizTalk Management database ===#

$Catalog = New-Object Microsoft.BizTalk.ExplorerOM.BtsCatalogExplorer
$MyBTSQLServer = “.” #substitute your SQL server name here
$Catalog.ConnectionString = “SERVER=$MyBTSQLServer;DATABASE=BizTalkMgmtDb;Integrated Security=SSPI”

#==================================================================#
#=== Register a trap handler to discard changes on exceptions ===#
#=== Execution will continue in the event we want to delete the ===#
#=== receive port. ===#
#==================================================================#

$Script:NoExceptionOccurred = $true
$ErrorActionPreference=”silentlycontinue”
trap
{
$Script:NoExceptionOccurred = $false
“Exception encountered:`r`n”; $_; “`r`nDiscarding changes and continuing execution so we can attempt to clean up the receive port…`r`n”
$Catalog.DiscardChanges()
}

#=== Create the new receive port ===#
#Write-Host “`r`nAttempting to create `”My Receive Port`”…”
#CreateReceivePort

# used on EDI1 to get lists of TRLG send/receive ports related to 204/990
# and create CSV file
cls
$showDate = Get-Date -DisplayHint Date
Write-Host “Started at $showDate”

Add-PSSnapIn -Name BiztalkFactory.PowerShell.Extensions #NOTE: Must be in 32-bit version of Powershell to use this SnapIn
#New-PSDrive -Name BizTalk -Root BizTalk:\ -PsProvider BizTalk -Instance EDI1-BTSDB2014\DB04 -Database BizTalkMgmtDb

Write-Host “— SendPorts —”
cd “Biztalk:\Applications\Echo.BSSR.Surface\Send Ports”
$SendPorts = Get-ChildItem
ForEach ($SendPort in $SendPorts)
{
#Write-Host $SendPort.Name $SendPort.Status
if (
($SendPort.Name.Contains(‘204’) -and $SendPort.Name.ToUpper().Contains(‘OUT’)) -or
($SendPort.Name.Contains(‘990’) -and $SendPort.Name.ToUpper().Contains(‘IN’))
)
{
## two formats of SendPorts spOutbound204XXXX and spSurfaceXXXoutbound204
Write-Host “Delete SendPort $($SendPort.Name)”
#DeleteSendPort($SendPort.Name)

}
}

Write-Host “— Receives —”
cd “Biztalk:\Applications\Echo.BSSR.Surface\Receive Ports”
$ReceivePorts = Get-ChildItem
ForEach ($ReceivePort in $ReceivePorts)
{
#Write-Host $ReceivePort.Name
if ($ReceivePort.Name.Contains(‘990’) -and $ReceivePort.Name.ToUpper().Contains(‘INBOUND’) )
{
Write-Host “Delete ReceivePort $($ReceivePort.Name )”
#DeleteReceivePort($ReceivePort.Name)

}
}

$showDate = Get-Date -DisplayHint Date
Write-Host “Completed at $showDate”