EAI Technologies & their Pitfalls

Parse XML with XmlReader (closing empty elements correctly)

2011-09-27T09:41:00.011+02:00

If you want to parse an XML document with the XmlReader, it might help to use this XmlReader example from MSDN. However, there's one thing missing: If you parse an XML with empty elements, such as:
<example1 />
<example2>bla</example2>

the example doesn't set the closing tags write, but like this:
<example1>
<example2>bla</example2>
</example1>

In order to omit this problem, you have to write the end elements in case that the reader parses an empty element:
case XmlNodeType.Element:
writer.WriteStartElement(reader.Name);
writer.WriteAttributes(reader, true);
if (reader.IsEmptyElement)
writer.WriteEndElement();
break;

VS/BTS Error "The system cannot find the file specified"

2011-09-23T09:43:00.003+02:00

If you receive the error message "The system cannot find the file specified." with the exception code 80070002 while adding a disassembling schema in Visual Studio/BizTalk, it is because VS does not find the assembly from the referenced project with the schema. Just build the referenced project, and then you should be able to add the schema.

Error details: The Messaging Engine encountered an error during the processing of one or more inbound messages.

2011-09-14T23:48:00.002+02:00

If you have created a flatfile schema, you might run into the following error:

A message received by adapter "FILE" on receive location "..." with URI "D:\dev\TestFlatfileDis\filedrop\*Copy.txt" is suspended.
Error details: The Messaging Engine encountered an error during the processing of one or more inbound messages.

In this case, it might be that your test file is running into an error while reading the lines:

Reason: Unexpected data found while looking for:
'\r\n'
The current definition being parsed is Root. The stream offset where the error occured is 13. The line number where the error occured is 3. The column where the error occured is 2.

To solve this, check that all the lines go until a certain column. If you can't ensure this, you could define the fields (at the end of the line) with MinOccurs=0, so that it can be omitted.

XML-Error Message "content is not allowed in prolog"

2011-09-07T10:12:00.004+02:00

When we created a BizTalk Mapping in VS2008, the compiler returned the error "line 1 colum 1 content is not allowed in prolog". Since the compiler told us that the error was detected in the very beginning (line 1 column 1), we needed a bit of time to see that actually the error was inside the document, where we had filled an element with a value although this shouldn't contain one.
So, if you get the same error message, check which fields you filled and if one of these shouldn't be filled.

32/64-bit Installation of Oracle Adapter

2011-09-06T12:59:00.004+02:00

After installing the Oracle Adapter, it might be that you run into errors in run-time, though you solved all the problems so that you could connect to Oracle in VS. ;-) I ran into the following error
System.IO.FileNotFoundException: Die Datei oder Assembly Oracle.DataAccess, Version=2.111.7.20, ...

Actually, the Oracle WCF adapter found the assembly 2.111.7.0, but was searching the Oracle.DataAccess.dll in the BizTalk installation folder:

LOG: Diese Bindung startet im default-Load-Kontext.
LOG: Die Anwendungskonfigurationsdatei wird verwendet: D:\Program Files (x86)\Microsoft BizTalk Server 2009\BTSNTSvc64.exe.Config
LOG: Die Computerkonfigurationsdatei von C:\WINDOWS\Microsoft.NET\Framework64\v2.0.50727\config\machine.config wird verwendet.
LOG: In der Anwendungskonfigurationsdatei wurde eine Umleitung gefunden. 2.111.7.0 wird nach 2.111.7.20 umgeleitet.
LOG: Verweis nach der Richtlinie: Oracle.DataAccess, Version=2.111.7.20, Culture=neutral, PublicKeyToken=89b483f429c47342
LOG: Download von neuem URL file:///D:/Program Files (x86)/Microsoft BizTalk Server 2009/Oracle.DataAccess.DLL.

The problem was inconsistent image-typing: While VS ran in 32bit, the BizTalk host instance ran in 64bit so that it didn't look in the GAC... Using a 32-bit host instance solved the problem; however, more elegant might be to install a 64-bit version of the Oracle.DataAccess (that however could cause problems with a 32-bit VS...)

Furthermore, deploy the policies "policy.2.x.Oracle.DataAccess" into the 32- and the 64-bit GAC to because both VS and the administration console of the BizTalk are searching for the assembly.

Installing Oracle WCF-Adapter

2011-09-05T14:45:00.013+02:00

In order to use the LOB WCF-Adapter for Oracle, you have to install an oracle client. Since BizTalk requests the assembly Oracle.DataAccess of version 2.111.7.0 or above, you have to select a correct Oracle client at least in version 11.2.0.21.

Install the client according to Steef-Jan Wigger's explanation. In %oracle%\product\11.1.0\client_1\odp.net\bin\2.x, you find the Oracle.DataAccess and you can put it to the GAC using OraProvCfg.exe that also redirects all assembly invocations to the actual one. (Otherwise, VS throws an error that it can't find version 2.111.7.0).

Additionally, you can add the sqlnet.ora und tnsnames.ora into %oracle%\product\11.1.0\client_1\Network\Admin. You find samples in the "Sample" folder.

ORA-1017: invalid name/password: When you connect from Visual Studio to the Oracle server, make sure to write the username in capital letters, even if the username is not written in capital letters in Oracle (otherwise you get an ORA-1017: invalid name/password error)!

Error Message: "Fehler beim Laden der Eigenschaftenliste anhand des Namespaces, oder Eigenschaften in der Liste nicht gefunden."

2011-09-02T11:06:00.004+02:00

On a German installation, the BizTalk server threw the following error message after parsing a message:

Fehler beim Laden der Eigenschaftenliste anhand des Namespaces, oder Eigenschaften in der Liste nicht gefunden, Überprüfen Sie, ob das Schema korrekt bereitgestellt wurde.

Actually, this error states already the problem quite good, which is that the message contains a promoted property that doesn't exist in the schema property. So, if you get this error message, go through your code and have a close look at all properties that you set and check whether you've spelled their names correctly.

Setting the mail address of Receiver in the Pipeline Component

2011-08-30T15:58:00.005+02:00

If you want to send a message with a static or dynamic SMTP send port, you can configure the mail address of the receiver in a pipeline component. Therefore, you have to promote OutboundTransportLocation in the message context of the message:

msgContext.Promote("OutboundTransportLocation", "http://schemas.microsoft.com/BizTalk/2003/system-properties", "mailto:" + mailAddress);

How to Filter in Orchestrations on Promoted Properties

2011-08-24T12:18:00.011+02:00

If you want to do direct-bounding into an orchestration (for example, because you're implementing an ESB where send ports and orchestrations subcribe directly to the message box), you would define filter expressions in the receive shape.

Basically, you can filter on schema properties that are referenced in the BizTalk project of your orchestration. However, to make them visible (and selectable) here, you have to publish them. You can do this in (at least) two ways:

First, you could promote a field in the incoming message and link the field to a promoted property. This option helps you if you filter on a field that is inside the message.
Second - if the data is not part of the message but a header information - you have to set the "property schema base" of the schema property to MessageContextPropertyBase. This way, it gets visible and you can filter on it.

Delete all the Data of a Partition

2011-06-06T12:36:00.016+02:00

If you want to delete blocks of data, you usually would like to delete blocks instead of deleting rows. If you at the same time use partitions to store your data, and all the data that you want to delete (e.g. all the data from 2010 and before), you can use the partition commands to realize this. Instead of deleting row by row with the delete-Command
delete * from table where timestamp < '2011-01-01'

you would then truncate the data of the partition(s) that contains this data (from 2010 and before). To do this, you have to create a staging table and then use the SWITCH TO command to move the data to this table. Both steps could be done using the SQL Server wizards. If you have created partitions on a table, you could right-click on the table name, choose "Storage", then "Manage partition":

In the wizard, you choose "Manage partitioned data in a sliding window scenario" and then enable "Switch Out":

The wizard composes now SQL commands to create a staging table with the same columns and indexes as the original table. However, to delete a partition's data you wouldn't need to create all the indexes, it's enough to create the columns. Then you have to invoce the SWITCH TO command to move the data to the staging table:

ALTER TABLE table SWITCH PARTITION 1 TO stagingTable

This is a meta data-operation, no data is moved physically, and thus the data movement doesn't cost a lot. To delete the data, you can then drop the table or truncate it. Both is a block operation and, thus, won't harm the database.

PS: In case, that other tables have foreign keys to this table, you would have to delete this data beforehand (since it might not make sense to have it without its references anyway). Most usually, you would have these referenced tables also partitioned so that you should think about deleting the partitions of referenced tables in the same transaction.

Doing Partition Elimination

2011-05-25T15:28:00.015+02:00

Let us assume, that we want to move old data to secondary filegroups. In this case, we would then partition on the timestamp of a row. However, our applications querys a lot on the primary key column "ID", so we prefer to make the ID instead of the timestamp the partition column.

The table therefore partitioned like this:

Partition 1: ID < 99 (before 01-February-2011)
Partition 2: ID 100..199 (February 2011)
Partition 3: ID 200..299 (March 2011)
Partition 4: ID 300..399 (April 2011)
Partition 5: ID > 400 (after 01-May-2011)

At the same time, there are a lot of queries with timely conditions such as
select * from table where timestamp > '2011-04-01' and error is not null

The SQL Server query optimzer would now search the whole table for entries that are younger then the 1st of April 2011 (and have the error column filled). Since the timestamp is not part of the partition column, the query optimizer doesn't know that all data from April 2011 and younger is in partition 4 and 5 and, hence, it can't do partition elimination but has to go through all partitions.

How can we help the query optimizer that he only has to look up the concrete partition.

Until now, I found three answers:

You can add a condition to the partition to the query:
select * from table where timestamp > '2011-04-01' and error is not null and $partition.function(ID)>=4
This way, the SQL Server goes through all rows with an clustered index seek and returns rows from partition 4 and greater. It's fast, but you have to change the query.
You can add a condition to the ID to the query:
You can also limit the query to the boundary value of the partition - adding and ID>=300, and thus, the SQL Server uses a clustered index scan, which is faster than a clustered index seek (see Scans vs. Seeks from Freedman), and furthermore, only reads through the partitions 4 and 5.
You could add a non-clustered index on the timestamp:
Thus, the ID is automatically included because it's in the clustered index. Then, you don't have to change your queries, and the SQL Server can use the index to find the IDs quite fast. This is the most convenient option because you don't change the query; however, the SQL Server needs more steps to find the result.

If you have more ideas, please let me know in a comment. :-)

Move a Table to another Filegroup

2011-05-19T18:18:00.008+02:00

In case you want to move a table to another filegroup, you would have to change the primary key of the table... and also all the foreign keys to that primary key.

This script allows you to do all this, it drops the constraints of the foreign keys, then it moves the primary key and then it creates again the constraints of the foreign keys.

You can just run the script - as a result you receive the needed SQL statements to move the table (the script itself doesn't make any changes!!).

declare @tablename varchar(50)
set @tablename = --'TableA
declare @newFilegroup varchar(50)
set @newFilegroup = --'FilegroupB'

declare @tableID int
select @tableID = object_id from sys.tables where name=@tablename

select 'BEGIN TRANSACTION'

UNION ALL

select 'ALTER TABLE [dbo].[' + t.name + '] DROP CONSTRAINT [' + fk.name + ']' from sys.tables t
inner join sys.foreign_keys fk on t.object_id = fk.parent_object_id
inner join sys.foreign_key_columns fkc on fk.object_id = fkc.constraint_object_id
inner join sys.tables tMove on fkc.referenced_object_id = tMove.object_id
where tMove.name = @tablename

UNION ALL select 'GO'

UNION ALL

select 'ALTER TABLE [dbo].[' + @tablename + '] DROP CONSTRAINT ' + kc.name + ' WITH (MOVE TO ' + @newFilegroup + ')' from sys.key_constraints kc where kc.parent_object_id = @tableID and kc.type='PK'

UNION ALL select 'GO'

UNION ALL

select 'ALTER TABLE [dbo].[' + @tablename + '] ADD CONSTRAINT ' + kc.name + ' PRIMARY KEY(' + c.name + ') ' from sys.key_constraints kc
inner join sys.columns c on kc.parent_object_id = c.object_id
where kc.parent_object_id = @tableID and c.is_identity = 1 and kc.type='PK'

UNION ALL select'GO'

UNION ALL

select 'ALTER TABLE [dbo].[' + t.name + '] WITH CHECK ADD CONSTRAINT [' + fk.name + '] FOREIGN KEY([' + cMove.name + ']) REFERENCES [dbo].[' + @tablename + '] ([' + c.name + '])' from sys.tables t
inner join sys.foreign_keys fk on t.object_id = fk.parent_object_id
inner join sys.foreign_key_columns fkc on fk.object_id = fkc.constraint_object_id
inner join sys.tables tMove on fkc.referenced_object_id = tMove.object_id
inner join sys.columns c on t.object_id = c.object_id
inner join sys.foreign_keys fkMove on fkMove.object_id=fkc.constraint_object_id
inner join sys.columns cMove on cMove.column_id=fkc.parent_column_id
where tMove.name = @tablename and c.is_identity = 1
and fkc.referenced_object_id=@tableID and fkc.parent_object_id=cMove.object_id

UNION ALL select 'GO'

UNION ALL

select 'ALTER TABLE [dbo].[' + t.name + '] CHECK CONSTRAINT [' + fk.name + ']' from sys.tables t
inner join sys.foreign_keys fk on t.object_id = fk.parent_object_id
inner join sys.foreign_key_columns fkc on fk.object_id = fkc.constraint_object_id
inner join sys.tables tMove on fkc.referenced_object_id = tMove.object_id
where tMove.name = @tablename

UNION ALL

select '--COMMIT'

The result of the script looks like this:

Have fun!

Eat Frozen Yogurt!

2011-05-18T15:17:00.004+02:00

My first post in this blog was about motivating software developers with chocolate.

No, I have to advertise a frozen yogurt cafe of friends in Hamburg-Winterhude, which offers delicious frozen yogurt - ice cream mixed with fruits or chocolates - and good gepa coffee (which since all times has motivated developers). If you can't go there by foot (Dorotheenstraße 99), you should at least visit them online: FrozenYogurtWelt bei Facebook and the FrozenYogurtWelt homepage.

Good articels on PARTITION

2011-05-11T13:22:00.012+02:00

Here are some articels about PARTITION in SQL Server 2008 that helped me:

Alan Granfield talks about Myths and Truths of partitioning.
Microsoft published a good white paper about partitioning and index strategies in SQL Server 2008.
A good explanation on how to move a partition into another filegroup comes from Dan Guzman.
Feodor Georgiev writes about how to risk losing data and how to have most down time.

And, a bit of propaganda for myself:

I wrote a guest post on Feodor's blog sqlconcept.com about how to generally handle new and old data with partitioning (using a Louis Vuitton and an army bag as an example).

Query a SQL Server Database while it is still restoring

2011-05-04T15:33:00.016+02:00

PARTITION is an SQL Server Enterprise Edition feature that can be used to spread data over different partitions, which can be put into different filegroups.
When you think about Backup and Restore strategies, a neat thing is that secondary filegroups can be set read-only and then don't have to be backuped permanently (simply because the data keeps unchanged). Thus, your backup runs faster, because you only backup readwrite filegroups, and thus the backup doesn't effect your database so much. Furthermore, you can do piecemeal restore after a disaster, which means that you can make the database available already after restoring the primary filegroup. So the users can query the restored part of the database until you restored all the filegroups of the database.

The following example shows how queries to the database work, if one filegroup isn't restored.

In the example, there are two tables with ID and CreateDate. The second table has a foreign key to the ID of the first table. The tables are partitioned with column ID at value 3, so that ID 1 and 2 of each table reside in partition 1, and ID 3 and 4 in partition 2. The first partition is on filegroup 1, the other on filegroup 2. A join query an the two tables returns this result:

In case of a crash, we would restore first the primary filegroup. If we run the same query against all the data in the tables, we get an error because not all data is available:
Msg 679, Level 16, State 1, Line 8
One of the partitions of index 'PK_Transfile' for table 'dbo.Transfile'(partition ID 72057594039238656) resides on a filegroup ("SECONDARY") that cannot be accessed because it is offline, restoring, or defunct. This may limit the query result.

In order to make your query run, you have to restrict it to the restored partitions. In this case, you have to restrict the query so that the ID columns of the tables are smaller or equal to 2. Since the partition column is known, the SQL processor knows that it only has to query the first partition and, thus, only enters the first, restored filegroup. The result then is not complete, but it doesn't return an error:

This way, you can use PARTITION to get your system run much faster, you can shorten your backup time and you can ease restore.

Error creating Flat File IDOC from XML IDOC

2011-01-04T11:24:00.007+01:00

We receive an SAP IDOC using the WCF adapter; afterwards, the disassembler uses that input file to create an XML document. When we later want to re-create an IDOC flat file from that XML document, the assembler does not create blanks for fields that had been empty in the input file - though they are marked as mandatory fields in the schema.

In this example, you can see the missing blanks in front of the "INVOIC" segment in the output file.

Input file - 2 blank segments in front of "INVOIC"
EDI_DC40 3000000000000526082700 3012 INVOIC01

XML - no tags for the 2 blank segments, which seems okay:
<Receive xmlns="http://Microsoft.LobServices.Sap/2007/03/Idoc/3/INVOIC01//40B/Receive">
<idocData>
<EDI_DC40 xmlns="http://Microsoft.LobServices.Sap/2007/03/Types/Idoc/3/INVOIC01//40B">
<TABNAM xmlns="http://Microsoft.LobServices.Sap/2007/03/Types/Idoc/Common/">EDI_DC40</TABNAM>
<MANDT xmlns="http://Microsoft.LobServices.Sap/2007/03/Types/Idoc/Common/">300</MANDT>
<DOCNUM xmlns="http://Microsoft.LobServices.Sap/2007/03/Types/Idoc/Common/">0000000000526082</DOCNUM>
<DOCREL xmlns="http://Microsoft.LobServices.Sap/2007/03/Types/Idoc/Common/">700</DOCREL>
<STATUS xmlns="http://Microsoft.LobServices.Sap/2007/03/Types/Idoc/Common/">30</STATUS>
<DIRECT xmlns="http://Microsoft.LobServices.Sap/2007/03/Types/Idoc/Common/">1</DIRECT>
<OUTMOD xmlns="http://Microsoft.LobServices.Sap/2007/03/Types/Idoc/Common/">2</OUTMOD>
<IDOCTYP xmlns="http://Microsoft.LobServices.Sap/2007/03/Types/Idoc/Common/">INVOIC01</IDOCTYP>

Output file - missing blank segments in front of the "INVOIC":
EDI_DC40 3000000000000526082700 3012INVOIC01

If anyone knows a solution, please comment. If we find one, we let you know this here.

Misleading error message part2: "Not enough storage is available to complete this operation" after changes settings with the BizTalk Console

2010-10-21T12:07:00.007+02:00

Recently (actually since we use our new cluster environment) we receive the this message from time to time after changing some settings with the console:

It finally turns out that restart of the EntSSO service solves the problem although teh eventlog messages lead into another direction (reinstall etc.).

Guest post: Misleading error message with SAP WCF custom adapter

2010-06-18T10:27:00.004+02:00

In an IDOC receive scenario using the WCF custom adapter with SAP binding we received the following error messages:

The adapter "WCF-Custom" raised an error message. Details "Microsoft.Adapters.SAP.RFCException: Details: ErrorCode=RFC_EXCEPTION. ErrorGroup=RFC_ERROR_APPLICATION_EXCEPTION. SapErrorMessage=OBJECT_UNKNOWN. AdapterErrorMessage=Error returned by RfcCallReceiveEx while calling RFC: IDOCTYPE_READ_COMPLETE..

The solution is quite simple as it resides in the SAP setting for the RFC connection, transaction SM59. Switching the communication type to Unicode solved the problem.

(This post is brougth to you by Franziska Kirschner, our great new team member)

Sabbatical

2009-12-25T18:58:00.003+01:00

I'm taking a sabbatical next year at Estrellas en la calle in Cochabamba, Bolivia, thus I won't post anything here in 2010.
- Christoph

One year later, I'm back. Thanks for continue posting!

Directly Create an EDIFACT Interchange

2009-12-08T09:19:00.012+01:00

If you want to create the EDIFACT interchange directly instead of using the built-in functionality of BizTalk, you can create your own schema and send it the EdiSendPipeline. When you use BizTalk's built-in batching functionality, you can see which schema BizTalk uses to prepare the interchange. As far as I see, you can build your custom interchange schema this way:

Import...

Microsoft.BizTalk.Edi.BaseArtefacts.EdifactServiceSchema.dll (with namespace "http://schemas.microsoft.com/Edi/EdifactServiceSchema")
the EDI schema, e.g. EFACT_D06A_INVOIC_2.0

Create...

root node "EdifactInterchangeXml"
Below root, Create attribut @DelimiterSetSerializedData and set fixed value to 39:-1:-1:43:58:63:42:44:-1 (if this is how UNA should look like).
Below root, create groups UNA, UNB, UNZ and reference the types from the EdifactServiceSchema
Below root, create group "TransactionSetGroup" and subgroup "TransactionSet"
Below "TransactionSet", create a group that references the EDIFACT schema
Below "TransactionSet", create attribute @TransactionSetActivityId
Below "TransactionSet", create attribute @DocType and set fixed value to http://schemas.microsoft.com/BizTalk/EDI/EDIFACT/2006#EFACT_D06A_INVOIC_2.0

The result looks like this:

Weired Error "This is often an indication that other memory is corrupt." executing pipeline component

2009-12-08T09:14:00.004+01:00

After migrating our biztalk app to 2009 we received a confusing error message everytime we dropped a file into a file receive location with a pipeline containing a bunch of pipeline components:

"There was a failure executing the receive pipeline:
...
Reason: Attempted to read or write protected memory. This is often an indication that other memory is corrupt"

After along irritating searching we finally debugged the pipeline component and find the root cause:
The call of

message.Context.Read(propertyName, propertyNamespace);

within an pipeline component must not be done with a "Null" value of "propertyNamespace". Obviously there is no internal check for this and the following call within the unsafe code leads into a Null reference call and the error message above.

Problems getting the current assembly in VS2008/BTS2009

2009-12-04T14:35:00.004+01:00

BizTalk 2009 seems to copy self-developed BizTalk assemblies into hidden system folders. If you run into the problem that your assemblies are not updated (although you deploy them in Biztalk, add them to the GAC, or save them to the pipeline components folder), it might be that BizTalk still references an old version of the assembly that is in a system folder.

In that case, find and delete the old version of the assembly and try again.

Sending E-Mails with multiple attachments with BizTalk 2006

2009-11-20T12:10:00.008+01:00

If you want to send an email with several attachments using the SMTP Adapter of BizTalk, you have to set the following context properties:

SMTP properties of the message: Subject, From, ...
MIME properties of each bodyparts: FileName and IsMIMEEncoded

The latter could look like this:
inmsg.BodyPart.PartProperties.Write("FileName", "http://schemas.microsoft.com/BizTalk/2003/mime-properties", "file.html");
inmsg.BodyPart.PartProperties.Write("IsMIMEEncoded", "http://schemas.microsoft.com/BizTalk/2003/mime-properties", false);

Errors exist for one or more children

2009-11-19T11:17:00.005+01:00

After changing an orchestration only little, the build process sometimes throws an exception that says "Erros exist for one or more children". This message seems a bit random, since I couldn't find a real problem, like a missing assembly, definition, or port.

In my case, it helped to recreate the construct shapes from the orchestration by deleting the shape, creating a new one, and adding the code of the deleted one. After that, the error disappeared.

PS: You can even find the error message also inside the orchestration file. It might help to watch the changes inside the code, while you modify the orchestration in the orchestration designer. This way, you get a feeling of how BizTalk creates the code from the designer, and you can also observe when the error disappears.

Is the Convoy Pattern Orchestration getting slow?

2009-11-06T10:11:00.007+01:00

In our team, we discussed whether our orchestrations that implement the convoy pattern are getting slower while processing message by message. A small test showed that this is not true, even if you call a sub-orchestration for each message. The time line moved like this in our test:

There is just one exception where processing needs longer, but the mean duration is not increasing.

The part '%messagepart%' of message '%multipart message%' contained a null value at the end of the construct block.

2009-10-23T11:58:00.005+02:00

BizTalk throws the following error when one tries to create a multipart message with a message part that was just created from a map in the same construct shape:

Shape name: Send_1
ShapeId: 11910fdc-f4a1-4391-b09d-6a9bd0275ff8
Exception thrown from: segment 1, progress 11
Inner exception: The part '%messagepart%' of message '%multipart message%' contained a null value at the end of the construct block.

Exception type: NullPartException
Source: Microsoft.XLANGs.Engine
Target Site: Void CheckNonNull()
The following is a stack trace that identifies the location where the exception occured

at Microsoft.XLANGs.Core.Part.CheckNonNull()

Somehow, BizTalk looses the value of the message part that was NOT mapped.

A work-around for this is to create a second construct shape. One just creates the message part in a mapping, and the second shape puts the message parts together.

Thanks, Christian!

If the Pipeline Component doesn't appear in the Pipeline Editor toolbox

2009-10-05T10:01:00.004+02:00

If your BizTalk pipeline component doesn't show up in the toolbox of the pipeline editor, you should check these things:

Is the GUID of the pipeline component the same in AssemblyInfo and in the class itself (it appears twice there)?
Is the right assembly name used when instanciating an object of ResourceManager?
Are you using the same namespace outside the class, in the properties, and in the ResourceManager?
Is the assembly copied to %biztalk installation%\pipeline components?
Are you looking for the right component type (encoder, decoder, ...) in the pipeline editor?

Archiving Table Entries

2009-09-10T17:27:00.004+02:00

If you want to backup table entries into a second table and delete them in the master table, you can use the folling SQL:

DELETE FROM masterTable
OUTPUT DELETED.* INTO archiveTable
FROM deletionTable
WHERE masterTable.ID=deletionTable.ID

while deletionTable contains the IDs of the entries, which should be deleted.

When using this, all entries of the masterTable, which are joined to the IDs in the deletionTable are copied to the archiveTable and then deleted.

Thanks, Ronny!

EDIFACT in BizTalk 2009

2009-06-03T19:09:00.001+02:00

In April, I talked at a meeting of the Swedish BizTalk User Group on EDIFACT and BizTalk. If you are interested, check my presentation on EDIFACT in BizTalk 2009.

Sending Big IDocs with the WCF-Custom-Adapter

2009-03-10T15:49:00.004+01:00

In MSDN, there's a good explanation how to connect BizTalk and SAP with the WCF-Custom-Adapter. However, if you want to send big IDocs, there are some boundaries:

If you use the action Idoc, you could get an exception like: "Failed to allocate a managed memory buffer of 123982965 bytes. The amount of available memory may be low."

If you use SendIdoc instead, you get an exception in the receive pipeline: "Attempt to pass a delimited field greater than the maximum field length of 50000000."

As soon as I find something out, I post it here.

Stream validates EDI code

2009-02-26T11:59:00.003+01:00

When using the EDISendPipeline in BTS2006 R2, that might be interesting:
My colleague Christian and me just found out that the EDI stream is validated by the stream reader while parsing the stream! So, when you work on the EDI stream which is produced by the EDI assembler, you get exceptions like "Error in serialization" if your EDI file is invalid. Quite clever how the programmers solved the validation of EDIFACT since it validates the file while returning it to the next step in the Send Pipeline.

Addition 2009-10-21:
Because of this, the EDI stream can't be read twice in following self-build pipeline components and BodyPart.Data.CanSeek is set the false.

Aggregating Values from a Hierarchy

2009-02-12T16:35:00.005+01:00

In a session about the SQL Server 2008 datatype HierarchyID, the question came up whether you can easily ask question such as: "If I store things (and sup-things and so on) with a number in a hierachy, can I sum up the numbers of a thing, including the subthings?"
The answer is, yes, you can, and here is how it goes. The following code shows a table which hierarchically stores websites and their clickrate.

-- create hierarchical table
create table websiteCalls
(
path hierarchyid,
filename nvarchar(100),
clickCount int
)

-- insert root node "index"
insert into websiteCalls values (hierarchyid::GetRoot(), 'index.html', 1000)
go

-- insert a level 1 entry "products"
declare @index hierarchyid
select @index = path from websiteCalls where filename='index.html'
insert into websiteCalls values (@index.GetDescendant(null, null), 'products.html', 200)

-- insert two more level 1 entries "customers" and "impressum"
declare @products hierarchyid
select @products = path from websiteCalls where filename='products.html'
insert into websiteCalls values (@index.GetDescendant(@products, null), 'customers.html', 100)
insert into websiteCalls values (@index.GetDescendant(null, @products), 'impressum.html', 10)

-- insert a "product1" on level 2
insert into websiteCalls values (@products.GetDescendant(null, null), 'product1.html', 300)
declare @product1 hierarchyid
select @product1 = path from websiteCalls where filename='product1.html'

-- insert two more level 2 entries
insert into websiteCalls values (@products.GetDescendant(@product1, null), 'product2.html', 50)
insert into websiteCalls values (@products.GetDescendant(null, @product1), 'product0.html', 100)
go

-- get all websites and their clickrates
select path.ToString(), filename, clickCount from websiteCalls

-- get the sum of clickrate number of all product-pages:
declare @products hierarchyid
select @products = path from websiteCalls where filename='products.html'
select SUM(clickcount) from websiteCalls where path.IsDescendantOf(@products) = 1 and path<>@products

Developing BizTalk with Professional Edition or Team Edition*

2009-01-27T10:26:00.002+01:00

If you have to choose a Visual Studio 2005 edition to develop BizTalk 2006 in combination with Team Foundation Server, you should keep in mind that the Team Edition has all the features of the Professional Edition and some more (quoted from Microsoft):

- Team Foundation Server CAL includfor accessing Team Foundation Server
- Team Foundation Server Workgroup Edition included when purchasing Visual Studio Team System Editions with an MSDN Premium Subscription
- 64-Bit Debugging (IA64) enables you to debug 64-bit applications running on Windows 64-bit computers remotely
- Code Analysis Tools such as Code Analysis Check-in Policy (which enforces developers to run a code analysis before a check-in of code)
- Code Profiling Tools like Hot Path (that helps finding bottle necks inside the code) and a Stand-Alone Profiler
- Code Coverage to measure the effectiveness of tests on a line-by-line or even a block-by-block basis
- Item templates to run Performance tests

That means, that you can connect to the Team Foundation Server from Visual Studio if you just buy the CAL-licence which includes the Team Explorer. However, you won't get the benefits of the Team Edition then.

*I'm using the term "Team Edition" as a short form for the Development Edition of Visual Studio Team System 2008.

Managing hierarchical data in SQL Server 2008

2009-01-25T00:40:00.006+01:00

There are roughly three different approaches to store and retrieve hierarchical data in SQL Server:

Parent-Child-Approach with Recursive TCE:
You can save the ID of a node's parent in an extra “parent column”:

Then you connect the referenced items with recursive Common Table Expressions (CTE).

HierarchyID data type:
Or you save the node’s hierarchy location with the new data type HierarchyID, which stores a path to a node and, thus, reveals the where in a tree the row occurs. A sample table looks like this:

The hierarchy is directly visible and, unlike in the simple parent-child-pattern, you have now the HierarchyID methods to work against the tree structure.

XML data type:
The third option is to represent the hierarchy in a XML document and store the instance in an xml column:

Doing this, you can retrieve information from the tree using XQuery.

This is meant to be a teaser... ;-) I'm going to write more about this the next days.

How does the SAP adapter retrieve the list of IDocs?

2009-01-20T14:53:00.007+01:00

At a customer, we had the problem that the SAP adapter (building on SAP .net Connector v1.0.2) didn't retrieve the IDoc that we wanted to call:

To analyse how the SAP adapter retrieves this list of IDocs, we traced the RFC communication between SAP and BizTalk (transaction ST05*) and found out that the SAP .net Connector uses the BAPIs RFC_SYSTEM_INFO and IDOCTYPES_FOR_MESTYPE_READ to get a list of IDocs which are released:

Using transaction SE37, we (thanks, Rüdiger!) could analyse the BAPI IDOCTYPES_FOR_MESTYPE_READ and see that it uses the table EDIMSG to get the IDocs. And their we saw that the BAPI doesn't call for "message types" instead of "basic types". And, guess what, the message type had a different name then the basic type (which makes perfectly sense) and, thus, couldn't be found.

* Tipp: When logging to SAP to trace, you have to use the same user that you use inside the Send Port / Receive Location.

How to solve Problems when accessing an IDoc with the SAP Adapter

2009-01-19T08:57:00.006+01:00

If you can't see an IDoc when you try to create a schema in Visual Studio using the SAP adapter (building on SAP .net connector 1.0.2), the problem is usually that the IDocs hasn't been published ("freigegeben"). You can check this here:

we60 - Check whether your IDoc is known in a specific SAP Release (we often had the problem that segments of an IDoc weren't known in a release)

we30 - You can check here whether all segements are published. Click on the segment, then "segment editor". There's a checkbox whether this segment is published.

we20 - If you can access the IDoc but can't send data to it, the IDoc might miss a partner agreement ("Partnervereinbarung").

Creating Functoids inside the XML Code of the Mapping File

2009-01-16T11:32:00.006+01:00

I had to create a BizTalk mapping with 246 Logical Existence-Functoids, which always checked whether a source field existed and then enabled or disabled the source field. You can save quite a lot of time (and I guess manual mistakes), if you use an text editor to create the XML manually to the transformation file (the format is called BTM and it's XML).

So, in case, your mapping looks like this, and you need many more functoids of that kind...

1) Open the transformation in some text editor.

2) Save the code of a new created functoid, for example:
<functoid functoidid="236" x-cell="59" y-cell="60" fid="317" name="Logical String" label=""><input-parameters></functoid>

3) Paste the code to a new file and put tabs into each line so that the code looks like this:

<functoid functoidid="[tab]236[tab]" x-cell="59" y-cell="[tab]60[tab]" fid="317" name="Logical String" label=""><input-parameters></functoid>

4) Now, copy the content to Excel and copy the lines so that the number, which should be pasted to extra columns, are increased-

5) Copy the content to a text editor, delete the tabs again and paste the code into the transformation file.

Ops! You should take care that the text editor that opens the BTM file externally doesn't change the unicode of the file.

Archive Messages with a BizTalk Pipeline to a Database

2008-08-21T16:07:00.006+02:00

In a BizTalk project, Mikael Håkansson and me had to create a receive pipeline in BizTalk, which archives messages to the database. A colleague of mine (Christian Brückner, which blogs here sometimes, too) inspired us to use the SQL-Command UPDATETEXT to store the messages in streams to the database. The result is quite cool and can be read on Mikael's blog post BizTalk SQL Message Archive component using Forward-Only Streaming.

Hot Fix Released to use MSMQ and EdiReceive Pipeline

2008-08-13T16:19:00.003+02:00

On April 1, 2008, Microsoft released a hot fix for the bug that we found when using the EDIReceive-Pipeline with MSMQ (see blog entry from September 2007). The hot fix can be downloaded here: http://support.microsoft.com/kb/948747/en-us.

Export/Import BizTalk Parties with Database functions

2008-04-18T09:15:00.007+02:00

Christian has already explained how to export and import large amounts of BizTalk parties with BindingImport. Now, we were thinking of exporting the BizTalk parties directly from one BizTalkMgmtDb to a new server without BizTalk parties. You can do this in six steps:

Check wether there are no parties yet saved on the target server (check bts_party)
Temporarily disable the identity flag for the primary key columns n_id of the tables bts_party and bts_party_alias on the target database.
Copy the table bts_party from the source system to the target database (you can't copy both tables in one step due to references' integrity).
Enable the identity flag for column n_id in table bts_party on the target database.
Copy the table bts_party_alias from the source system to the target database.
Enable the identity flag for column n_id in table bts_party_alias on the target database.

There are also other party tables such as bts_party_sendport, bts_enlistedparty, bts_enlistedparty_operation_mapping, and bts_enlistedparty_port_mapping. However, those connect parties to sendports, role links, mappings and ports, and should be rather configured than directly changed inside the database.

If you also want to adapt more EDIFACT configurations, you have to migrate the tables that start with "EdiPartner", too!

Martin, have fun!

2008-03-14T11:47:00.003+01:00

Sometimes, error messages don't really help. ;-)

EDI: Table passed to EDI_SEGMENTS_ADD_BLOCK is empty.

2008-02-21T15:28:00.006+01:00

The error "EDI: Table passed to EDI_SEGMENTS_ADD_BLOCK is empty"* was thrown by the SAP .NET connector after sending an IDoc to SAP. The reason was that we've set data fields inside the EDI_DC40 element that SAP had to fill on its own, such as

DOCNUM
CREDAT
CRETIM
RCVPOR
SNDPOR
DOCNUM from the DATAHEADERREC element

Now, we are leaving these empty, and the IDoc can be received by SAP.

Thus, if you get the same error, check which fields you should set, and which SAP should set on its own.

----------
* in German SAP systems: "EDI: Die an EDI_SEGMENTS_ADD_BLOCK übergebene Tabelle ist leer"

Short Installation guide to integrate SAP-BizTalk connectivity in Visual Studo 2005

2008-02-20T18:36:00.003+01:00

To integrate SAP connectivity into BizTalk 2006 R2 and Visual Studio 2005, you have to follow these steps:

1. Install the runtime version of SAP .NET Connector (NCO) for Visual Studio v1.0.x which is a prerequisite for (2) - you can download the connector from the SAP Marketplace Web site.

2. Install BizTalk Adapter v2.0 for mySAP Buesiness Suite which is available here: https://www.ms2.cn/downloads/details.aspx?FamilyId=7DBF88DE-8C23-4B25-A389-D5ACF1B1FCBE&displaylang=en

3. Install the adapter's Service Pack 1 and ignore the obsolete Error 6000-message by clicking "OK".

Using BizTalk's command xpath(msg,string) correctly

2008-02-04T15:21:00.003+01:00

If you want to use BizTalk's xpath()-method in an Expression shape of Visual Studio's BizTalk Orchestration designer, you have to think of the return of the xpath carefully.

If you use the following:

[string] s = xpath(msg1, "/*[local-name()='Root' and namespace-uri()='http://TestBiztalkXPathCommand.Schema1'] /*[local-name()='name' and namespace-uri()='']")

to get the value at node "name", you'll receive the following error while running the orchestration:

...
Inner exception: There is an error in the XML document.
Exception type: InvalidOperationException
Source: System.Xml
[...]
Additional error information:
was not expected.
[...]
Target Site: System.Object Read_string()

The reason is that this XPath statements navigates to an XML node and, thus, an object of the class System.Xml.XmlNode is returned (and not a String). Since the XPath statement is not analyzed while building the DLL, the error occurs while running primary.

Thus, you should add /text() to your XPath statement, so that a string is returned. In this case, the following would work successfully:

[string] s = xpath(msg1, "/*[local-name()='Root' and namespace-uri()='http://TestBiztalkXPathCommand.Schema1'] /*[local-name()='name' and namespace-uri()='']/text()")

Charles Young's blogpost on xpath() was helpful to fix this problem.

Addition (June 3, 2008) : My colleague Christian found out that you should invoke some mysterious "string()"-method inside the xpath statement:

xpathExpression = "/*[local-name()='Root' and namespace-uri()='http://TestBiztalkXPathCommand.Schema1'] /*[local-name()='name' and namespace-uri()='']";
[string] s = xpath(msg1, "string(" + xpathExpression + "/text())")

Thus, you get less XPath errors (I don't know yet how that exactly works; but I know that string(...) is not an XPath expression).

Using CASE in SQL Queries

2008-02-04T09:02:00.001+01:00

This morning, I've got to know the CASE functionality of SQL Server (which surely exists in other databases, too). With CASE you can set values for a column under specified conditions, e.g.

SELECT
Column1,
Column2 = CASE
WHEN Column1 = '1' THEN 'Y'
ELSE 'N'
END
FROM Table1

Thus, column2 will be set with 'Y' if column1 has the value '1'; otherweise 'N' will be set.

PS: An overview by Craig S Mullins helps to understand this method in more detail.

Visual Studio throws error while building: The process cannot access the file as it is being used by another process.

2008-01-30T10:05:00.000+01:00

The error "The process cannot access the file as it is being used by another process." is thrown by Visual Studio (here 2005) if an assembly, that is used by the building process, is blocked by another process.

A useful program to find the blocking process is Process Explorer (v11.04). Furthermore, you should check other sessions on the development system and, also, other machines that could have the same solution opened remotely.

Shrinking SQL Server Log file

2007-12-17T16:49:00.000+01:00

The logfile of the BizTalk Server Message Box database (BiztalkMsgBoxDb) is rapidly growing if you the recovery model is set to "full".

At first you should try this (taken from here):

ALTER DATABASE BiztalkMsgBoxDb
SET RECOVERY SIMPLE;
GO

DBCC SHRINKFILE (BiztalkMsgBoxDb_log, 1);
GO

ALTER DATABASE BiztalkMsgBoxDb
SET RECOVERY FULL;
GO

In some cases you're not able to shrink this file.
This may refer to some dead processes and unfinished transactions.

Your can check for the oldest transactions using:

DBCC OPENTRAN ('BizTalkMsgBoxDb')

If you find a very old transaction there you may kill the PID with the Acitvity Monitor of the SQL Server Studio.
Repeat the last two steps until all old transactions are disappeared.
Finally do
BACKUP LOG BizTalkMsgBoxDb WITH TRUNCATE_ONLY

DBCC Shrinkfile ('BizTalkMsgBoxDb_log',2) (where "2" is the targeted Logfile size)

and enjoy the new log sizes.

Error message can be found in BizTalk's Message Flow window

2007-11-19T14:07:00.000+01:00

Sometimes it seems like BizTalk is trying to hide error messages. So, if you can't find any sufficient message neither at the suspended instances nor in the event viewer, here is another source of information:

1. Open the Message Flow using the instance's context menu of HAT or Administration Console
2. Use the link "Show tracked Orchestration Exceptions".

This will show you an error text, which actually explains the error. :-)

Error: BizTalk receives"empty" messages from WebService

2007-11-12T18:13:00.000+01:00

Biztalk could receive "empty" messages from the Web Service, as it is deployed. Empty in this case means that the message looks like this:

<?xml version="1.0"?>
<account xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" xmlns="http://schemas.microsoft.com/crm/2006/WebServices" />

As you can see, only the root element "account" was given (so it's not really empty).

However, the system that invokes the Web Service is definitly setting the input parameters.

This happens, if the input schema of the orchestration is changed, but the Web Service is not created and deployed afterwards (e.g. using Visual Studio's wizard). Then, the out-dated Web Service receives unexpected parameters and passes only the root node to BizTalk.

Workaround for the BizTalk 2006/CRM Problem stating "Error in XML document (2, 2)"

2007-10-11T13:49:00.000+02:00

After upgrading from BizTalk 2004 to BizTalk 2006, an error occured when receiving an error from the CRM WebService saying that there is an "Error in XML document (2, 2)".

The message received by the orchestration changed, as the namespace was now given (the red part has not been given in BizTalk 2004):

<?xml version="1.0" ?>
<guid xmlns="http://schemas.microsoft.com/crm/2006/WebServices" >E4096CF2-2777-DC11-B068-001A4BAF607E</guid>

And, the namespace http://schemas.microsoft.com/crm/2006/WebServices indeed does not have a root name entry for "guid" (as you can see in the Administration Console/Application X/Schema).

I unfortunately don't know the reason for this, but, at least, we found a work around: We wrote a CRM wrapper, that encapsulates the functionality of envocing the CRM Web Service. This wrapper is published as a Web Service that receives exactly the same object BusinessEntity like the CRM Web Service does. Than it invokes the CRM Web Services and returns the GUID. BizTalk than has no problem taking this GUID and work furtheron.

HAT error: ActiveView.ColumnAxis is null or not an object

2007-10-01T09:21:00.000+02:00

When opening the BizTalk HAT, the following error occurs, if you have not installed Office XP's Web Components.

Install it from here: http://www.microsoft.com/downloads/details.aspx?FamilyID=982B0359-0A86-4FB2-A7EE-5F3A499515DD&displaylang=en

BizTalk Error 916 duplicate 'Microsoft.XLANGs.BaseTypes.DesignerPosition' attribute

2007-09-25T14:13:00.000+02:00

If you receive the following error while building an BizTalk orchestration:

Error 916 duplicate 'Microsoft.XLANGs.BaseTypes.DesignerPosition' attribute

you now know that it's because you've copied a shape from orchestration A' to orchestration A, while A' has been a copy of A before. Because (only?) this copy produces a new orchestration shape in A that has the same GUID as the already existing shape.

I guess, this error message pops up really, really seldom :-)

Export/Import large amounts of Parties using BindingImport

2007-09-19T11:35:00.001+02:00

The party management of Biztalk gets a little bit slow and unhandy with a a large amount of trading Partners.

Exporting about 12.000 BizTalk parties with the Binding Export tool takes about 6 hours on a fast machine resulting in a 200 MB XML binding file.

For an import in smaller slices you can split the binding file in smaller pieces with a small program using XPathNavigator and XmlTextWriter:
XPathDocument xPathDocument = new XPathDocument (@"Bindingfile.xml");
XPathNavigator nav = xPathCocument.CreateNavigator();
XPathNodeIterator partyCollection = nav.Select("/BindingInfo/PartyCollection/Party");

Iterate over the partyCollection and create XML documents
XmlTextWriter xmlWriter = new XmlTextWriter(@"SmallBinding.xml",Encoding.UTF8);
xmlWriter.WriteStartDocument();
xmlWriter.WriteStartElement("BindingInfo");
xmlWriter.WriteStartElement("PartyCollection");
...

Finally use BTS-Task to import the slice:

Process process = new Process();
process.StartInfo.FileName = @"C:\...\BTSTask.exe";
process.StartInfo.Arguments = "importbindings -Source:" + @"SmallBinding.xml";
process.Start();
process.WaitForExit();

Limitations in EDI pipeline and Direct Submit Port

2007-09-13T11:24:00.002+02:00

We found two limitations with BizTalk 2006 R2 Beta2 (and RTM Bit's):

(a) Invoke a direct submit port inside a biztalk orchestration (you have to implement and invoke a web service for direct submit)

Thanks to Vishy (http://geekswithblogs.net/Vishy/archive/2005/10/06/56217.aspx)

(b) Using the EDI send or receive pipelines in conjunction with the MSMQ adapter (you can use the FILE adapter, though)

There was a failure executing the receive pipeline: "Microsoft.BizTalk.Edi.DefaultPipelines.EdiReceive, Microsoft.BizTalk.Edi.EdiPipelines, Version=3.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" Source: "Pipeline " Receive Port: "ZES.ReceiveEdi.FL_Batch" URI: "FORMATNAME:DIRECT=OS:.\PRIVATE$\TESTMSMQ" Reason: The integer property value is not valid.

Microsoft will release a hot fix for this issue!

Sending large messages to MSMQ via Dynamic Send Ports

2007-09-12T10:49:00.000+02:00

I had troubles figuring out how to send large messages to MSMQ when using a dynamic send port in BizTalk. Developing a connection to MSMQ with "late binding" is straightforward, as you just have to configure a send port - however, doing this via code is less easy.

However, if you follow these steps, it's not a big clue:

1. Create a queue in Computer Management > Services and Applications > Message Queuing.

2. In your BizTalk Projekt, import the library Microsoft.BizTalk.Adapter.MSMQ.MsmqAdapterProperties.dll, stored in %Program Files%\Microsoft BizTalk Server 2006.

3. In a construct message shape, assign the following port properties to the output message:

// to set the admin queue:
Message(MSMQ.AdministrationQueue) = @"FORMATNAME:DIRECT=OS:.\PRIVATE$\MSMQADMINQUEUE";

// to make it transactional:
Message(MSMQ.Transactional) = true;
Message(MSMQ.MaximumMessageSize) = 10240;
Message(MSMQ.SegmentationSupport) = true;

// to assign a special name:
Message(MSMQ.Label) = System.Convert.ToString("ABC");

... and define the target MSMQ queue in the send port object:

SendPort(Microsoft.XLANGs.BaseTypes.Address) = @"msmq://FORMATNAME:DIRECT=OS:.\PRIVATE$\TESTMSMQ";

Voilà!

Thanks to Scott Colestock (http://www.traceofthought.net/PermaLink,guid,cbd9b038-f952-479c-85b2-84398c451623.aspx) and MSDN's MSMQ Properties Page (http://msdn2.microsoft.com/en-us/library/aa577593.aspx)

Opener - or: This developer is (not only) driven by chocolate

2007-09-12T09:19:00.000+02:00

My first entry shows an important piece of motivation: an extra large chocolate that I just received as a gift from one of my colleagues. So, if you ever need an answer to a question, don't forget to send some chocolate! ;-)

In my daily work as a software developer, I'm used to fight with computer systems. After finding a lot of solutions in blogs, I decided to make up my own blog to publish this or that solution to a pitfall that I discover (or think to discover).

Have fun!