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.

BizTalk Mapper and Altova MapForce are both tools used to data from one schema to another. A schema internally is treated as XML, but may represent a flat file, CSV, a SQL table to stored proc, or a web service request/response message. Typically the job of a B2B developer is to map data from one format to the other. In this blog, I compare mapping from schema “Flight” to schema “FlightNew” using both tools.

BizTalk

Below is a sample BizTalk Map.

BizTalk Example Map

BizTalk Example Map

BizTalk has various functions called Functoids. Internally, these are implemented in C# embedded in the XSLT code. There is a red concatenate Functoid in the sample above, and two Scripting Component Functoids. A Scripting Component allows you to type or paste-in your own custom C# code. So this is how I implemented the difference in the two dates.

BizTalk_Script_Functoid_CSharp

You can right-click on the map properties in the Solution Explorer, and set the desired XML input file. Then right-click the map name in Solution Explorer, and click “Test Map”. After the map runs, you will see some text in the Output window. You can CNTL-CLICK on the output file, then you will then see the results below.

BizTalk - Results of "Test Map"

BizTalk – Results of “Test Map”

Altova MapForce

Altova MapForce - Full Screen

Altova MapForce – Full Screen

MapForce has a “datetime-diff” function, but the output has to go to a field defined with a type of xs:duration. I had actually never heard of this data type before. You can see the “concat” function in XSLT is essentially identical to the one in BizTalk.

Altova MapForce - Zoom In on Mapping

Altova MapForce – Zoom In on Mapping

Click the “Output” tab under the map to view the conversion of the data to the new schema format:

Altova MapForce - Sample Output

Altova MapForce – Sample Output

 

However, some functions are only supported in C# and Java. When switching to XSLT, I found the function below to be unavailable. You can see the striped lines appear in the function below.

Altova_MapForce_NonXSLT_Function

I didn’t have time to try to reimplement that function in XSLT.  So below is the same map without being able to compute the difference between ScheduledDateTIme and ActualDateTime.

When in XSLT mode, and XSLT tab appears below the mapper. Below is the view of the XSLT code generated from the visual tool.

One of the advantages of MapForce is that it can build XSLT, and has a two-way editor. You can edit the visual mapper, or edit the XSLT code directly, and the other automatically syncs.

Altova MapForce XSLT Tab

Altova MapForce XSLT Tab

Altova can also generate Java, C# or C++ code to perform the transformation.  You can take that code, and call it from your application.  MapForce has EDI functionality built in, so you can map for example 850 Purchase Orders into XML data.

One of the advantages of MapForce is that it can build XSLT, and has a two-way editor. You can edit the visual mapper, or edit the XSLT code directly, and the other automatically syncs.

By the way, BizTalk does support XSLT, but once you switch to what they call “Custom XSLT”, you can no longer use the visual “GUI” editor.

So in this overview, we see two ways to map data using two different but similar tools.  We saw that the scripting functions can be quite different in the two products.

Sometimes you need to mass replace all the text string for all files in a directory, or at least all files matching some file mask.

Here’s a quick sample that I put together.

As a BizTalk consultant, I deal with data coming in from customers or trading partners. Sometimes, that data needs to be scrubbed. We were doing multiple rounds of testing, and the trading partner was going to put a fix in place in a few days, but in the meantime, I was having to hand edit each and every file manually, before putting the file into BizTalk. I was really getting tired of that process and wrote the script below.

# Fix various issues in the certain EDI files 
cls 
$path = "c:\MyPath\"
$files = get-ChildItem $path -filter "850*.edi" 
#$files  #use this to just display all the filenames 
foreach ($file in $files) 
{
   Write-Host "`n`nfixing file= $($file.Name)"
   $filetext = Get-Content $file.FullName -Raw   
   # The -Raw (line above) option brings all text into a string, without dividing into lines 
   Write-Host "Old Text in file: $($file.Name)" 
   Write-Host $filetext

   #example of regular text 
   $filetextNew = $filetext     -replace "Texas",        "TEXAS"
   #example of changing EDI tags
   $filetextNew = $filetextNew  -replace "\^WACO", "\^DALLAS"
   $filetextNew = $filetextNew  -replace "\^EXCELLENT",   "\^EU"
   $filetextNew = $filetextNew  -replace "\^MODUSE",      "\^MU"
   $filetextNew = $filetextNew  -replace "\^LIGHTUSE",    "\^LU"
   $filetextNew = $filetextNew  -replace "\^HEAVYUSE",    "\^HU"

   Write-Host "NEW:" 
   Write-Host $filetext 
   Set-Content $file.FullName $filetextNew
}

I was modifying an EDI file, so I wanted to make sure that the string I was modifying started with the caret symbol. So for example, I really wanted to change “^MODUSE” to “^MU”. Since that caret symbol has special meaning in RegEx (Regular Expressions), I had to put the backslash in front of it as an escape character. So I added the first line to change “Texas” to “TEXAS” to show that the backslahs and caret symbol are not needed for normal text replacement.

The one bug I had in the code above was specifying $file.Name instead of $file.FullName. It almost drove me crazy. It seemed to be returning the filename itself, rather than the contents of the file; probably because that file didn’t exist in the current directory in which the PowerShell script itself was running.

In the past, I used to use a utility called “BK-Replace’Em”, which is now called by the more generic name “Replace Text”. You can download a free copy from EcoByte here. It can do the same thing as above, without writing any code. The only thing is you need to be able to download and install it on your server, and I didn’t want to do that on the various servers that I’m currently working on.

I was playing with SQLPS for the first time and wanted to save my samples for future use.

Code

cls 
Write-Host "Start" 
Import-Module SQLPS –DisableNameChecking
Write-Host "Done with Import-Module" 
cd SQLSERVER:\
DIR

Write-Host "`n`nList of Instances (not working?)" 
cd SQLSERVER:\SQL\localhost 
Get-ChildItem | Select instancename  

Write-Host "`n`nList of Databases on Local Server default instance" 
cd SQLSERVER:\sql\localhost\DEFAULT\Databases   # specify "DEFAULT" if you have no instance name 
Get-ChildItem | Select name 

Write-Host "`n`nAlternate List of Databases on Local Server default instance" 
Invoke-SQLcmd -Server '.' -Database master 'select name, database_id, create_date from sys.databases' | Format-Table


Write-Host "`n`nList of tables in NealDemo Database" 
cd SQLSERVER:\sql\localhost\DEFAULT\Databases\NealDemo\Tables
Get-ChildItem 

Write-Host "`n`nRun some SQL Command" 
Invoke-Sqlcmd -Query "SELECT @@VERSION, db_name();"


#$sqlpath = "SQLSERVER:\sql\localhost\DEFAULT\Databases`nealDemo\Tables"
#dir
Write-Host "End" 

Output

Start
Done with Import-Module

Name            Root                           Description                             
----            ----                           -----------                             
DAC             SQLSERVER:\DAC                 SQL Server Data-Tier Application        
                                               Component                               
DataCollection  SQLSERVER:\DataCollection      SQL Server Data Collection              
SQLPolicy       SQLSERVER:\SQLPolicy           SQL Server Policy Management            
Utility         SQLSERVER:\Utility             SQL Server Utility                      
SQLRegistration SQLSERVER:\SQLRegistration     SQL Server Registrations                
SQL             SQLSERVER:\SQL                 SQL Server Database Engine              
SSIS            SQLSERVER:\SSIS                SQL Server Integration Services         
XEvent          SQLSERVER:\XEvent              SQL Server Extended Events              
DatabaseXEvent  SQLSERVER:\DatabaseXEvent      SQL Server Extended Events              
SQLAS           SQLSERVER:\SQLAS               SQL Server Analysis Services            


List of Instances

InstanceName : 



List of Databases on Local Server default instance

Name : EMPLOYEES


Name : NealDemo


Name : ReportServer


Name : ReportServerTempDB


Name : VideoGenerator



Alternate List of Databases on Local Server default instance
WARNING: Using provider context. Server = localhost.



name                                                                        database_id create_date                                
----                                                                        ----------- -----------                                
master                                                                                1 4/8/2003 9:13:36 AM                        
tempdb                                                                                2 4/16/2015 11:42:24 PM                      
model                                                                                 3 4/8/2003 9:13:36 AM                        
msdb                                                                                  4 2/20/2014 8:49:38 PM                       
ReportServer                                                                          5 12/11/2014 5:42:06 PM                      
ReportServerTempDB                                                                    6 12/11/2014 5:42:07 PM                      
NealDemo                                                                              7 1/8/2015 1:31:34 PM                        
VideoGenerator                                                                        8 1/12/2015 2:08:16 PM                       
EMPLOYEES                                                                             9 1/20/2015 2:21:54 PM                       




List of tables in NealDemo Database

Schema                       Name                           Created               
------                       ----                           -------               
Audit                        AuditAllExclusions             2/4/2015 8:21 AM      
Audit                        AuditBaseTables                2/4/2015 8:21 AM      
Audit                        AuditDetail                    2/4/2015 8:21 AM      
Audit                        AuditDetailArchive             2/4/2015 8:21 AM      
Audit                        AuditHeader                    2/4/2015 8:21 AM      
Audit                        AuditHeaderArchive             2/4/2015 8:21 AM      
Audit                        AuditSettings                  2/4/2015 8:21 AM      
Audit                        SchemaAudit                    2/4/2015 8:21 AM      
dbo                          Employee                       2/4/2015 3:55 PM      


Run some SQL Command
WARNING: Using provider context. Server = localhost, Database = NealDemo.

Column1 : Microsoft SQL Server 2014 - 12.0.2000.8 (X64) 
              Feb 20 2014 20:04:26 
              Copyright (c) Microsoft Corporation
              Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
          
Column2 : NealDemo

End



PS SQLSERVER:\sql\localhost\DEFAULT\Databases\NealDemo\Tables> 

Suppose you have a large list of items that you want to put into a SQL in list statement, like this:


where firstname in (
'Aaron',
'Abbey',
'Abe'
)

But the data you have in is Excel or a text file, and doesn’t have the quotes and commas around it.

You can quickly modify the data using the RegEx Replace All feature of NotePad++.

Data to change and the Replace Commands

 

NotepadPlusPlus_RegEx_Replace_For_SQL_In_List

Data to be changed, and then do CNTL-H or Search/Replace… from the top menu.

 

This is what it means.  The “Find What” is set to (.*).  That means find all characters on each line, and capture it. The parentheses are the capture symbols, everything between them is captured.  The Dot means any-character, and the * means 0 or more of those characters.

The “Replace with” is set to ‘\1’.  The backslash-one refers to the first capture in parentheses from the “Find what” box above.  Then the other symbols happen to be what I wanted to add, a single-quote at the beginning, and a single-quote at the end.

 

Results

Results - After the Replace

Results – After the Replace

 

where firstname in (
'Aaron',
'Abbey',
'Abe',
)

The only issue is the last name in the list will also have a comma after it.  You will just have to change that manually.  (I.e. remove the comma after “Abe” above).

 

From time to time, I need to transfer data from one database to another. RedGate Data Compare is my to tool of choice, but not all clients have a license for it ($495 for SQL Data Compare, $1795 for the Developer Suite). I also like SQL Database Compare if I need to sync DDL (tables, indexes, constraints, stored procs, functions, etc…) from one system and the other.

The other day, as a BizTalk developer, I had to copy a lookup table from one system to an existing database on another system.  So I couldn’t just backup/restore the file.  I tried some of the tools in SQL, which I must admit I don’t use often, and I was getting a connectivity issue; maybe the firewalls between or DMZ (not 100% sure).

Years ago, I remember a nice C# WinForm program that allowed you to enter a SQL statement, then would generate SQL Insert Statements from it.  I was having trouble finding it, but found two other alternatives.

Solution #1 – All SQL – Generating SQL Statements in SQL Server – I like this one because it was all written in T-SQL.  The site provides a StoredProc that you add to your database and execute with a table name. You turn off grid mode, i.e. put your output in text mode, and save or copy/paste the results less the header.  This allowed me to get done what I needed this week in just about 10 minutes.  I just took the generated SQL statements, copied them to the other server, and ran them in SSMS.  To run it, you just run the stored proc and pass the table name:

exec InsertGenerator MyTableName

Out of the box, it doesn’t give you the ability to select a subset of rows with a where clause.

Solution #2 – C# – Generating SQL Statements in C# – This one is a C# solution if your prefer that language.  It’s a console program, so no WinForm/GUI.  I didn’t actually try this one.  I’m still hoping to find the GUI version that I found a few years ago.  I think it was on CodeProject as well.

In my case, it was a code lookup table used by a BizTalk map.  I took the insert statements as well as the create table statement, and added to the BizTalk project (in a “doc” or “prereqs” subfolder so future developers on different environments would be able to load up the table quickly. You can include other files such as .sql or .txt into a BizTalk project, but they are not compiled of course.  That way, they are checked into your source code repository.

Below is an example of a RedGate Data Compare.  You can specify two databases, then it shows you the tables. You can select all tables or individual select the tables you want to compare.  It then compares the rows of those tables.  However, note that the table has to exist on both databases for it to compare the table and generate SQL Insert Statements.  SQL Database Compare will help you to sync up the tables.

RedGate SQL Data Compare

RedGate SQL Data Compare

Yes, today we are stepping into our magic time machine, and going back to BizTalk 2004. Why? Because that’s what I had to do at work today. And I forgot a few basics from that long ago time. I needed to test a map in Visual Studio – and I was totally lost. I googled, couldn’t find anything easily. Had to ask a co-worker, even though I used BizTalk 2004 and even used to teach it. Thus the reason for this blog.

The trick is that you have to do right-click (on the map) then click “Properties”. A separate “Properties Window” pops-up, different than the Visual Studio component properties (that we are used to seeing by default in the lower right corner of the screen).

Here’s what it looks like:

BT2004 Map Properties

BT2004 Map Properties

The properties screen looks like this:

BT2004 Map Property Pages

BT2004 Map Property Pages

From there just type or paste your filename in the “TestMap Input Instance”. Then later, when you do a right-click “Test Map” on your map, that’s the file it will use for input.

Just to compare, in BT2010 with VS2010, it looks like this:

BT2010 - Test Map - Map Properties

BT2010 – Test Map – Map Properties

You still use the Output window to see the results of the test-map, CNTL-CLICK on the output file name to open it and see the results. If you get a lot of errors or warnings, you could try setting “Validate TestMap Input” and “Validate TestMap Output” to False.

Sometimes, in a large company with undocumented servers, you find an IP Address, and you need to know what server it is. For example, the other day I found a URL of a web service. I pinged the URL, which gives the IP Address. But I needed to know the server name:

nbstat -a xx.xx.xx.xx (where xx.xx.xx.xx is your IP Address)
The -a option “lists the remote machine’s name table give its name).

nbtstat_lookup_servername_from_IPAddress

When you Deploy a BizTalk project, it has to update a BizTalk SQL database, which by default is called BizTalkMgmtDB.

Suppose you have been using BizTalk on the same server for days, weeks, or months, and you open someone else’s project, change the server name, and get the error below. That’s the specific case I’m covering here. At the bottom of the blog, I refer you to other possible solutions if you’ve never got it to work before.

Errors

Text error:

error DEPLOY: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) The network path was not found 

Same Error in Screen shot of Visual Studio. Once in Error List and once in View Output.
BizTalk-Deploy-SQL-server-not-found_ViewOutputBizTalk-Deploy-SQL-server-not-found_ErrorList

Tricky Solution – Did you Save the Changes?

Ok, suppose you know the server is up, you just deployed another project, and all the settings are identical.
Here is a trap that I fell into more than once.

I opened a BizTalk solution from our source control. The prior developer had checked in the code with his machine name it. While I think that’s a bad practice, I have to continue and fix it.
BizTalk_Deployment_Properties_Before

So I changed from his server (machine) name, to (LOCAL), which means the server I’m running on right now.
BizTalk_Deployment_Properties_After

Then I did a deploy and got the error. I did several times, and was getting very frustrated.

The settings you see on the screen are retrieved (and eventually saved) in the .btuser file.

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="4.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup Condition="'$(Configuration)|$(Platform)' == 'Debug|AnyCPU'">
    <Server>ABC1234</Server>
    <ConfigurationDatabase>BizTalkMgmtDb</ConfigurationDatabase>
    <ApplicationName>xxxx webservices</ApplicationName>
    <Redeploy>True</Redeploy>
    <Register>True</Register>
    <RestartHostInstances>True</RestartHostInstances>
  </PropertyGroup>


Finally, when watching this file in NotePad++, I realized that changing the value on the screen does not save it in the file! You have to first either do a manual “save”. Not even backing out of the Deployment Properties seems to cause the save to happen. Thus, when I was compiling, I was using the old values on the disk, and not the values that looked plainly obvious on the screen.

Other Solutions

If this is the first time you have deployed, the issue could be caused by other reasons.

Refer to this blog for general causes of not being to connect to a SQL server: Pinal Dave SQL Authority site – could not open a connection to SQL server

Hope this helps! Just be sure and click SAVE before you DEPLOY.