MS-SQL Query to get count of Spool size filter by MessageType

Take a scenario you are in middle of Heavy message load flooded in BizTalk system on your Production environment and you want to find which type of message are heavily available in Spool table which need attention to be cleanup or process.

By using the following MS-SQL Query you will get the list of records separated by Message Type in Spool Table with Message Type name.

USE BizTalkMsgBoxDb

SELECT nvcMessageType, count(*) AS MessageCount 
FROM dbo.Spool (NOLOCK)
GROUP BY nvcMessageType
ORDER BY MessageCount DESC

Hope this Helps!!!!

Bulk copy files from one folder to another using powershell

Suppose you have a thousands of files in one folder and its sub folders and you have given a task to filter out few files which you already identified is available but to find each file and copy them to other folder is quite tedious one. especially urgency of files filter is very high.

to overcome this issue and resolve this situation very smoothly i have come up with given power shell command which i am hoping to helpful to you people as well.

$file_list = Get-Content C:\fileslist.txt
$search_folder = "C:\SourceFolder"
$destination_folder = "C:\Backup\DestinationFolder"

foreach ($file in $file_list) {
    $file_to_move = Get-ChildItem -Path $search_folder -Filter $file -Recurse -ErrorAction SilentlyContinue -Force | % { $_.FullName}
    if ($file_to_move) {
        Move-Item $file_to_move $destination_folder
    }
}

all you need to do is fill your list of files in filelist.txt file and run the below power shell command. after successful completion you will able to see the filtered files in your destination folder location.


Hope this Helps!!!! 

Know your right MS-DTEexc version using the Powershell

Some times when you encounter with some weird error stating the wrong version of MS-DTExec you are using, your first course of action should be to check the version you are calling is right or not.

Error:
   Code: 0xC000F427
   Source: Receive SS_SALES
   Description: To run a SSIS package outside of SQL Server Data Tools you must
install Receive SS_SALES of Integration Services or higher.


With the help of following Powershell script command you are able to identify list of all available/installed on your machine.

PS C:\Windows\system32> Get-ChildItem C:\Progra*\dtexec.exe -Recurse | %{
$_.FullName
$version = &$_;
$version -like "*Version*"
Write-Host
}

Hope this Helps!!!!

For-each usage with Looping, Conditional and Value Mapping Functoids in BizTalk

In one requirement client requested to develop destination schema having specific output format.

Problem : xsl:for-each statment to be put at the parent field of destination schema while using the Looping, Conditional and Value Mapping Functoids

Solution : When you use a Looping, Conditional functoid, or a Value Mapping functoid, an xsl:for-each statement is generated in the compiled map. If the child field of the destination schema has unbounded maximum occurrences, the xsl:for-each statement is put at the child field. If the child field does not have unbounded maximum occurrences, the xsl:for-each statement is put at the parent field of the child field.

However, because the location of the xsl:for-each statement affects the map result, you may want the xsl:for-each statement to be put at the child field of the destination schema, regardless of whether the maximum occurrence of the child field is set to 1.

You can control the placement of the xsl:for-each statement by modifying the value of the TreatElementsAsRecords attribute in the map (.btm) file

     Syntax : <mapsource TreatElementsAsRecords="Yes" ></mapsource>


When this attribute is set to Yes, the xsl:for-each statement is put at the child field of the destination schema, regardless of whether the maximum occurrence of the child field is set to 1.

Hope this Helps!!!

Evalute Itinerary Performance in BizTalk

In one of the requirement client have requested to have evalution of Itinerary Performance from development stand point.

Problem : how can we track itinerary performance with ESB Toolkit

Solution : out of the box ESB Toolkit have capability to track the Itinerary with predefined performance counters

ItineraryBeginTime
ItineraryEndTime
ItineraryState
MessageDirection
ServiceType
ServiceisRequestResponse
ServiceState
ItineraryName
ESBServiceName

here from this we can concentrate only on (ItineraryBeginTime, ItineraryEndTime, ItineraryState) this performance counters available in BAMPrimaryImport database.

There is a step in ESB Toolkit installation where one need to deploy Itinerary Services BAM defination as given in below steps

C:\Program Files (x86)\Microsoft BizTalk Server 2010\Tracking>bm deploy-all -Def
initionFile:"C:\Program Files (x86)\Microsoft BizTalk ESB Toolkit 2.1\Bam\Micros
oft.BizTalk.ESB.BAM.Itinerary.xml"

Once you run above command you can see several tables bam_ItineraryServiceActivity in BAMPrimaryImport database.

now in Itinerary Designer for shape where we need to enable tracking we can achive the same by setting the Tracking Enabled="True" option.

In BAMPrimaryImport database we have table called bam_ItineraryServiceActivity_Completed we can utilize the ItinearryBeginTime and ItineraryEndTime column to take the difference in Seconds/NanoSeconds for messages and take average of same and calculate average time taken, highest time taken and lowest time taken to process the message using Itinerary.

Here is the example:

SELECT DATEDIFF(NS,ItineraryBeginTime,ItineraryEndTime) FROM [BAMPrimaryImport].[dbo].[bam_ItineraryServiceActivity_Completed]

Hope this Helps!!!

Execute custom command with Deployment Framework (BTDF) in BizTalk

Some time back one requirement came to me to run some custom command execution using the Deployment Framework or BTDF.

Problem : any way, where in we can keep an orchestration in the unenlisted state using BTDF

Solution : above requirement can be fulfill with two approches

1) we can keep the property  State="Unenlisted" in PortBindingsMaster.xml file for orchestration which you wanted to keep unenlisted, but make sure StartApplication set to false.

2) using WMI script we can Unenlist the orchestration post the successful deployment using the BTDF

In Target we can create one custom Target which run post deployment in BTDF, following is the sample,

<target name="CustomTargetDeploy">
    <Exec Command="my_command_to_execute" />

</target>

instead of text "my_command_to_execute" we can write our own command like (rename, copy, del etc.) we can also call the batch file as well, following is the command line arguments for Unenlisting the orchestration.


 cscript.exe "c:\Program Files (x86)\Microsoft BizTalk Server 2010\SDK\Samples\Admin\WMI\Stop Orchestration\VBScript\StopOrch.vbs" "myNameSpaceName.OrchestrationName" "myAppName" Unenlist

we need to save above command as OrchUnenlist.bat then call this in Custom Targest.

Hope this Helps!!!

ItineraryState does not updating with 'Complete' status in BizTalk

I have got one of the requirement wherein i have to analyze the performance of Itinerary. like how much time overall an Itinerary can take.

Problem: Itinerary status does not updates with 'Complete' even though Itinerary flow is completed.

Solution : Whenever you face such issues first checkpoint that you need to look at is see if you have configured the right pipeline on your send port(static or dynamic) and make sure you have selected the Microsoft.Practices.ESB pipelines like ItinerarySend or ItinerarySendPassThrough or any of your custom pipeline having the Dispatcher component.

Hope this helps!!!