by community-syndication | Feb 9, 2006 | BizTalk Community Blogs via Syndication
Since the Backup History Delete step does not clean up the backup files, I have make a stored procedure that cleans up the files also, add the stored procedure below to the BizTalk Management Database and change step3 of the backup job by adding “AndFiles“ to the end of the exiting stored prorcedure call.
CREATE PROCEDURE [dbo].[sp_DeleteBackupHistoryAndFiles] @DaysToKeep smallint = null
AS
BEGIN
set nocount on
IF @DaysToKeep IS NULL OR @DaysToKeep <= 0
RETURN
/*
Only delete full sets
If a set spans a day such that some items fall into the deleted group and the other don’t don’t delete the set
*/
DECLARE DeleteBackupFiles CURSOR
FOR SELECT ‘del ‘ + [BackupFileLocation] + ‘\’ + [BackupFileName] FROM [adm_BackupHistory]
WHERE datediff( dd, [BackupDateTime], getdate() ) >= @DaysToKeep
AND [BackupSetId] NOT IN ( SELECT [BackupSetId] FROM [dbo].[adm_BackupHistory] [h2] WHERE [h2].[BackupSetId] = [BackupSetId] AND datediff( dd, [h2].[BackupDateTime], getdate() ) < @DaysToKeep )
DECLARE @cmd varchar(400)
OPEN DeleteBackupFiles
FETCH NEXT FROM DeleteBackupFiles INTO @cmd
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT
delete from [adm_BackupHistory] WHERE CURRENT OF DeleteBackupFiles
print @cmd
END
FETCH NEXT FROM DeleteBackupFiles INTO @cmd
END
CLOSE DeleteBackupFiles
DEALLOCATE DeleteBackupFiles
END
GO
by community-syndication | Feb 8, 2006 | BizTalk Community Blogs via Syndication
Do you use virtual images? I use them almost daily and when you work with something like BizTalk Server….it’s a must! I’ve always been a fan of Virtual PC and really did not have any issues with it…until recently. Jonathan Summers and I were preparing for the BizTalk First Look Clinics and we were frustrated by the performance of Virtual PC. Jonathan has struggled with Virtual PC on this Dell Laptop and there are some other grumblings out there on the Internet.
Personally, I didn’t have too many issues until recently. At the launch in Houston, my keyboard gave out on my VPC during my presentation. My Virtual Machines started to lag in speed and then all of the sudden, I started getting a 2-3 second pause about every 10 seconds when using VPC….very frustruating. I said no worries and just installed Virtual Server R2. Not too many issues at first, but then my Virtual Machine Remote Client started acting up. I started getting double keystrokes when typing and performance was still just ok. I ended up just remoting into the virtual machines, but this wasn’t the greatest method either.
So after all of this, it was time to give VMWare a try. I used VMWare a long time ago and it wasn’t a pleasant experience. I downloaded Workstation 5.5, installed and everything was fine. Upon download, I also downloaded a Virtual Machine Importer tool so I could reuse my existing VPC machines. I ran the importer and pulled in one of my BizTalk 2006 Beta 2 machines. Holy crap…the VMWare machine was just as fast as my host machine…something I never experienced with VPC. I think I’ve been sold on VMWare and here are a few of the benefits:
- Faster performance – I’ve been able to load Windows 2003 and XP with no issues
- Display support is better
- USB support is included
- Faster pause or hibernation – you can pause a machine with the click of a button and it’s paused
- Faster start up when paused
- No more loud beeps when you do something wrong – anyone have this issue with VMRC?
- Better disk allocation – VMWare can be set up to use 2 Gig Files automatically which will lead to less fragmentation
- Did I mention faster performance?
I still think Virtual PC is a good product, but my demo yesterday was done using VMWare and I think I’ll be using it in the future. Anyone else have any issues with Virtual PC?
It didn’t take very long to convert, but hboy was there a difference in the performance. The VMWare machine was just as fast as my host machine…something I never experienced with Virtual PC. I was able to load the VMWare Tools which allowed me to have better displays and it also supports USB.
by community-syndication | Feb 8, 2006 | BizTalk Community Blogs via Syndication
Recently I had to map a document with many thousand rows. I could not split the document because before I could split it, the document’s nodes had to be sorted.
With such large files you generally test it using a small subset to avoid waiting for maps to complete, I built an XSLT which worked great, I thought.
When you use a select filter such as “not(KeyValue=preceding-sibling::row/ KeyValue)” you end up with a huge performance hit the larger the document gets. My map went from 2 seconds for 50 rows to 10 minutes for a few thousand.
How to improve performance when you have large XML files to map that you can’t split? Try using xsl:key instead, which builds an index of keys from which you can much more efficiently select.
Here is a sample XSLT that demonstrates how to use the xsl:key:
<?xml version=”1.0″ encoding=”UTF-8″ ?>
<xsl:stylesheet xmlns:xsl=”http://www.w3.org/1999/XSL/Transform” version=”1.0″ xmlns:ns0=”http://Conversion.schemas”>
<xsl:output method=”xml” indent=”no” />
<xsl:key name=”NumberKey” match=”/*[local-name()=’top’ and namespace-uri()=’http://biztalk/Conversion.schemas’]/*[local-name()=’row’ and namespace-uri()=”]”
use=”keyValue” />
<xsl:template match=”/”>
<ns0:Rows>
<xsl:for-each select=”/*[local-name()=’top’ and namespace-uri()=’http://biztalk/Conversion.schemas’]/*[local-name()=’row’ and namespace-uri()=” and generate-id(.) = generate-id(key(‘NumberKey’, keyValue)[1])]”>
<xsl:variable name=”current_Number” select=”keyValue” />
<Data>
<keyValue>
<xsl:value-of select=”$current_Number” />
</keyValue>
<xsl:for-each select=”//row[keyValue=$current_Number]”>
<Part>
<PartID>
<xsl:value-of select=”nr_data” />
</PartID>
</Part>
</xsl:for-each>
</Data>
</xsl:for-each>
</ns0:Rows>
</xsl:template>
</xsl:stylesheet>
by community-syndication | Feb 6, 2006 | BizTalk Community Blogs via Syndication
Here’s a cool mapping tip I found on Vijendra’s blog.
While mapping an element from the source schema to the target schema, it is possible to copy either the value contained within the element or the element name itself.
You achieve this by setting the Source Links property of the link connecting the elements as shown below.
In the above figure above the value mapped to the target element will be “CustomerName”.
The values that can be set are:
1. Copy text value – which is the default that copies the content of the element
2. Copy name – copies the name of the source node instead of its value
3. Copy text and subcontent value – concatenates the value of all child nodes
Now you might be wondering what is this 3rd value (Copy text and subcontent value) that can be set…
This is used if you want to concatenate all the child element values of the source node into a single element in the target as shown below.
Here is an example:
Input Message
<ns0:Root xmlns:ns0=”http://MapTest.SourceSchema“>
<Record>
<FirstName>FirstName_0</FirstName>
<LastName>LastName_0</LastName>
<Street>Street_0</Street>
<City>City_0</City>
</Record>
<Record>
<FirstName>FirstName_1</FirstName>
<LastName>LastName_1</LastName>
<Street>Street_1</Street>
<City>City_1</City>
</Record>
</ns0:Root>
Output Message
<ns0:Root xmlns:ns0=”http://MapTest.DestinationSchema“>
<Record>
<Data>FirstName_0LastName_0Street_0City_0</Data>
</Record>
<Record>
<Data>FirstName_1LastName_1Street_1City_1</Data>
</Record>
</ns0:Root>
Now here is another interesting mapping
The mapping above transforms the elements in the source schema into a key-value pair as shown below.
Each element node from the source schema links to the two elements in the target schema, one link copies the node name and the other copies the node value.
Input Message
<ns0:Root xmlns:ns0=”http://MapTest.SourceSchema“>
<Record>
<FirstName>FirstName_0</FirstName>
<LastName>LastName_0</LastName>
<Street>Street_0</Street>
<City>City_0</City>
</Record>
</ns0:Root>
Output Message
<ns0:Root xmlns:ns0=”http://MapTest.KeyValuePair“>
<Record>
<FieldName>FirstName</FieldName>
<FieldValue>FirstName_0</FieldValue>
</Record>
<Record>
<FieldName>LastName</FieldName>
<FieldValue>LastName_0</FieldValue>
</Record>
<Record>
<FieldName>Street</FieldName>
<FieldValue>Street_0</FieldValue>
</Record>
<Record>
<FieldName>City</FieldName>
<FieldValue>City_0</FieldValue>
</Record>
</ns0:Root>
by community-syndication | Feb 5, 2006 | BizTalk Community Blogs via Syndication
Sometimes you receive a batch file containing multiple records and you have to split each record into a separate message and process them individually.
This kind of splitting of a batch file is also known as debatching.
Depending on your requirement, you can either debatch an incoming message inside the pipeline or inside the orchestration.
The technique used to debatch a message inside the pipeline depends on whether the input is an XML file or a flat file.
For an XML file, you will need to use an XML disassembler, an envelop schema and a document schema, which I will discuss some other time on separate blog post / article.
Here I will show you how you can debatch a flat file.
Let us assume that we need to debatch a flat file that contains the following structure.
Field1,Field2,Field3,Field4,Field5,Field6
Field1,Field2,Field3,Field4,Field5,Field6
Field1,Field2,Field3,Field4,Field5,Field6
Field1,Field2,Field3,Field4,Field5,Field6
Field1,Field2,Field3,Field4,Field5,Field6
Field1,Field2,Field3,Field4,Field5,Field6
First you need to create a flat file schema that looks similar to the one shown in the figure below.
Make sure you have specified the correct child delimiters for the Root (0x0D 0x0A) node as well as the Record (,) node.
Now select the Record node and set its MaxOccurs property to 1. This is actually the key to debatching. The flat file disassembler will automatically separate each record into individual message.
Next, as you would normally do, add a receive pipeline to the project and add a flat file disassembler and set the Document schema property to the schema we created.
That’s all there is to it.
Now suppose you have a flat file that also has a header record or a trailer (footer) record or both as shown below.
FirstName,LastName,RegNo,School,City
SubjectName1,Score1
SubjectName2,Score2
SubjectName3,Score3
SubjectName4,Score4
SubjectName5,Score5
SubjectName6,Score6
SubjectName7,Score7
SubjectName8,Score8
TotalScore
You would normally create a flat file schema as shown below to parse as one single message.
But for the purpose of debatching the body records, you need to create 3 separate schemas as shown below. Make sure the MaxOccurs of the Body record is set to 1.
And in the flat file disassembler, you need to set the Header schema, Document schema and Trailer schema properties appropriately.
Note: You might need to set a unique Tag Identifier property for each of the schemas, and also prefix the same tag identifier on the records as shown below, so that the flat file parser can distinguish the header, body and footer records.
HFirstName,LastName,RegNo,School,City
BSubjectName1,Score1
BSubjectName2,Score2
BSubjectName3,Score3
BSubjectName4,Score4
BSubjectName5,Score5
BSubjectName6,Score6
BSubjectName7,Score7
BSubjectName8,Score8
FTotalScore
It is possible to preserve the header record into the message context, by setting the Preserve header property of the flat file disassembler to true, so that you can use it as a header record for each of the debatched message.
To use the preserved header, you need to set the Header schema property of the flat file assembler to the same header schema you used in the disassembler. Here’s how the output will look.
HFirstName,LastName,RegNo,School,City
BSubjectName1,Score1
HFirstName,LastName,RegNo,School,City
BSubjectName2,Score2
HFirstName,LastName,RegNo,School,City
BSubjectName3,Score3
HFirstName,LastName,RegNo,School,City
BSubjectName4,Score4
HFirstName,LastName,RegNo,School,City
BSubjectName5,Score5
HFirstName,LastName,RegNo,School,City
BSubjectName6,Score6
HFirstName,LastName,RegNo,School,City
BSubjectName7,Score7
HFirstName,LastName,RegNo,School,City
BSubjectName8,Score8
by community-syndication | Feb 4, 2006 | BizTalk Community Blogs via Syndication
I had to use the FileSystemWatcher class these days and I found out that it raises multiple duplicate events. If you are not familiar with FileSystemWatcher class, this class allows you to watch a particular folder and receive notifications/events every time there is change happening in that folder. You can subcribe to all sorts of events: file created, file changed, file renamed, file accessed, directory created, directory renamed, directory accessed, etc.
The problem is that multiple duplicate events are being raised. For instance, if I copy a file over an existing file, instead of raising one file changed event, the FileSystemWatcher object will raise 5 !!! such file change events that are identical. If you are creating a file, the FileSystemWatcher class will raise a file created event and at least a file changed event.
I think that some of these issues are caused by the Win32 APIs and they are normal, others (like the file createad and file changed events when creating a file) are probably caused by applications. Many applications will first create an empty file and then they will update the file with the file content hence the file created, file changed events being raised when creating a new document.
This can be worked around by queueing the events (let’s say over a 1 second period) and then remove any duplicates. And this is what I did. I implemented my own version of FileSystemWatcher class (named DelayedFileSystemWatcher) that mimics MOST of the FileSystemWatcher behavior and it removes duplicate events.
DelayedFileSystemWatcher class will delay processing of any events that are being raised. All events that are raised (except error events) by the inner FileSystemWatcher object will be queued to a collection. The ElapsedEventHandler method will be invoked automatically (through a System.Timer.Timer event) every 1 second. This method will analyze all events in the queue. The first time an event is found, it will be marked as “Delayed” and it won’t be processed. The next time the method is executed, the already “Delayed” events will be raised but first all events that are duplicate of “Delayed” events will be removed. An event in the queue will either be delayed (processing postponed for next timer event), raised (it’s already delayed and it’s the first event of his kind), or deleted (delayed or not but there was an event in front of it that was similar/duplicate).So this class mimics FileSystemWatcher but it removes duplicate events that show up within 2 timer events. A second event is duplicate of a first event if both events are of the same type and have all properties equal or if the second event is a an update/changed event, first event is a create event and both events refer to the same file.
See code file attached. DISCLAIMER: Use this code at your own risk. No support is provided and this code has NOT been tested.
[Last file update – February 09, 2006]
