Hi all

I have changed the config of my blog software (dasBlog) so all comments have to be approved by me. I hate to have to do this, but apparently, the upgrade to a newer version has made it easy for spammers to write silly comments with links to all sorts of weird web sites (I guess they are weird - naturally, I have never clicked on one...). I have already delete 5 inappropriate comments to posts, and they seem to come faster and faster.

I will ask the programmers of dasBlog to see what is happening, and then disable the approval flow once I have it solved.

I apologize for the inconvenience.

--
eliasen

Hi all

So, it is time for the second part of the series about using the FarPoint BizTalk adapter for Excel spreadsheets. You can find my first post in the series, which was about the installation of the component here.

So, this post is about the wizard that guides you through creating a schema for an Excel spreadsheet.

I created a simple spreadsheet to test with. It has two sheets, which you can see here:

Basically, two sheets - one with order lines and one with comments. So, firing up the wizard:

The first thing to do is to add a new item to your project, and choose the new schema type "Spreadsheet Schema Wizard". The wizard fires up automatically, when you click "Add".

The first screen of the wizard isn't really a surprise It wants to you tell it which file to use as a base for the schema, and give a target namespace and inform it about what code page to use.

When browsing for files, I noticed that the components apparently not only deals with spreadsheets (Excel 97-2003 as well as 2007) but also delimited files. So note to my self: Look at that functionality later on - maybe it is better than BizTalks built-in support for that, or perhaps more suitable in some situations. Maybe that's a blog post that will appear at some point

So, a few more settings to set, all of which are described in the documentation.

Now, it shows me the data in the first sheet of the spreadsheet. It has removed all cells that it has decided are not used for data. Now, I need to select the cells with data in them, like this:

and when I click on the next sheet (Comments), I get to select data from that sheet as well:

Notice, that I can only select rows - I can not select single cells or leave some columns out.

The next step is to select names for the columns, choose whether they should be elements or attributes and also the data type of the columns.

There are four data types available, double, float, datetime and string.

Just to find the difference between the float and double, I chose one of each in my example and clicked "Finish".

The resulting schema looks like the one above. For each sheet, there is a sheetname attribute, a header record and a record for the data, which is reoccurring. The double and float elements were translated into the xs:float and xs:double types... not really surprising, you might say

Looking at the properties of the schema, the path to the base spreadsheet has been pre filled for you in the "Input Instance Filename" and the type is set to "Native".

When validating the instance, I get this XML, which looks like I expected it to.

So, to sum up, the wizard is really simple to use and it takes basically no time to create the schema.

The major thing I would like to see improved is that I can only have one type of data in one sheet, meaning that the data in all rows must be for instance order lines, inventory items, or something like that. I can't have an order header and the order lines in the same sheet, and I can't have a sheet with an order header which spans multiple lines. This really restricts the spreadsheets that can be parsed.

My next post in the series will be about the runtime, where I will setup a running instance of my project and see how it functions at runtime.

--
eliasen

Hi all

For those of you, who use Togi as a Twitter client, there is now a Danish translation of it available for download... and, as you will notice on the page, I did the Danish translation Although... I use the English version myself... but that is besides the point

--
eliasen

Hi all

So, I finally decided to try out the FarPoint BizTalk adapter for Excel spreadsheets. It's always nice to have tried as many adapters as possible, so I can use this knowledge when talking to customers.

This post is the first of a series of posts about this product. The first post is about installation and the basic functionality. The next posts will go deeper into separate functionality.

Let me just make one thing clear before I begin: I am not in any way affiliated with FarPoint Technologies, nor are they paying me anything to write these posts.

So, to begin; The installation was easy. Just a next-next-finish wizard. All the information needed from you is the product code, a serial number, and the installation folder, if you want to change that.

After installation, I have the functionality I would expect from this kind of product:

1. Pipeline components to use in pipelines
   
2. Schema extension for the schemas
   
3. Wizard to help me create the schemas
   
4. Documentation
   

So, to sum up:

1. The installation is easy.
2. The architecture of the solution seems to be exactly what I would expect, ie. pipeline components, schema extensions and a wizard.

My next post in the series will take a deeper look at the wizard for creating schemas for use by the pipeline disassembler and assembler.

--
eliasen

Hi all

In almost all multiple server installations of BizTalk I have encountered, there has been issues with MSDTC. MSDTC is Microsofts product for handling distributed transactions, meaning transactions that span multiple servers. BizTalk uses this in high scale, when running transactions against SQL Server, to maintain consistency in BizTalks databases.

All issues with MSDTC are solvable - sometimes it is just hard to figure out what is wrong.

First of all, always use the DTCTester tool at http://support.microsoft.com/kb/293799 to test your MSDTC installation. If this tool reports no errors and you are still having issues, then most likely, MSDTC isn't the cause of your issues.

If something is wrong with MSDTC, I have encountered four major issues:

1. MSDTC doesn't run on either of the server. Solve this by starting MSDTC. Steps to start MSDTC (Note, that the MMC snapin is buggy, and it might appear that the "Component Services" node has no children... but it does, trust me ):
1. Go to "Administrative Tools" => "Component Services"
2. Go to "Component Services" => "Computers" => "My Computer"
3. Right click "My Computer" and choose "Start MS DTC".
   
2. MSDTC isn't configured for network access on both servers. Solve this in "add/remove windows components" here:
   
3. The two servers have the same MS DTC ID. This ocurs if both servers are clones of the same server or if one of the ervers is a clone of the other server. Usually, when cloning servers, sysprep is used to clear out those errors, but in case it hasn't been used, here is how you fix it:
1. Run "msdtc -uninstall" from a command prompt
2. reboot
3. Run "msdtc -install" from a command prompt
4. reboot
4. You can't ping the servers by hostname, which is required. This basically means, that from both servers, you need to be able to ping the other server by hostname - pinging by IP address isn't enough. If you can't ping by hostname, you have two options:
1. Get the network administrator to update your DNS
2. Enter new information into the hosts file in c:\windows\system32\drivers\etc

Hope this helps.

--
eliasen

Hi all

Finally, I have managed to end the upgrading-hell it has been. I am now up to version 2.2.8279.16125 of dasBlog, and this is currently the latest release. I didn't have the guts to upgrade directly from 1.9 to 2.2, so I went over 2.0 and 2.1 to get there.

To me, everything looks ok - let me know if you discover anything else.

--
eliasen

Hi all

I have just upgraded dasBlog to 2.1.8102.813.

I hope this hasn't introduced weird behavior. Please contact me if you encounter errors or issues.

Thanks

--
eliasen

Hi all

I have just upgraded to dasBlog version 2.0.7226.0.

I am hoping it went without issues, but would appreciate an email if you discover any issues.

Thanks.

--
eliasen

Hi all

The CSD/BizTalk Product Group is conducting a survey to assess customer usage of the Business Rules Engine (BRE).

They have sent a link to us MVP's to get our opinion, but would also like the opinion of BizTalk consultants, BizTalk customers, and so on.

If you can spare a few minutes, please take the survey at https://live.datstat.com/MSCSD-Collector/Survey.ashx?Name=BRE_Usage_Survey_Blog

--
eliasen

Hi all

Today, a former customer of mine called me and had a question. She is using updategrams to insert rows into a table in SQL Server, and she had a problem with dates. The input CSV sometimes had an empty string for a date.

This would translate in the XML to an element with an empty string inside it. This would then get mapped to an attribute for that column in the table, and when SQL Server read the updategram, the 01-01-1900 was inserted into the date field because no other value was specified.

The customer wants null to be inserted instead. She asked me how to do this, and I said that that was easy - I would send her an example later this night.

So I started preparing my example, and it turns out, that my approach only works for elements and records - I have never before had to suppress an attribute, so I wasn't aware of this. My approach being, bye the way, that I connect a logical functoid to the destination node that I don't want created under some circumstances. But a logical functoid can not be connected to an attribute in the destination schema. Bummer.

So I eventually went with a custom scripting functoid, and my example ended up like this:

Input schema:

Just a plain schema with three elements, all required.

The output schema:

This schema has three attributes to simulate the updategram and also an element just to show the approach for elements.

The map ended up like this:

Element1 goes to att1 and Element3 goes to att3. No magic there.

As for element2 in the output, I have used to functoids to get it to be created only when a string is present in the source. The first functoid (the red one) is a "String Size" functoid, which returns the length of a string. The green functoid is a "Not equal to" functoid, which has the string length as one parameter and the constant "0" as the other parameter. The order of these parameters do not matter. So basically, at runtime, the element2 element is created if and only if, the length of the string in Element2 is larger than 0. For this particular customer, I could have used the "Logical Date" functoid instead of the combination of these two functoids, since that will return true if the input string is a date, which is exactly what I want. But the above solution is more general and applies to a lot more scenarios. Downside being, of course, that the above solution will accept a "abc" as a string, and since the length is larger than 0, it will get to the output.

Anyway, the customer didn't have elements, but attributes. for this I had to use a custom scripting functoid with this code:

Basically, this call template takes one parameter, being the element in the source that might be empty. It then check the value to see if it is different from the empty string, and if it is not the empty string, an attribute is created and the value for the attribute is set to the input parameter.

Now, as a side node, the input my customer has is a CSV file, so actually, in this case you can also take advantage of a nice little property on the schema. To demonstrate, I have created a flat file schema for a CSV input:

As you can see in the properties window, I have set "Suppress Empty Nodes" to "Yes". When BizTalk parses the flat file, the nodes that would be created as empty nodes now aren't created at all. This means, that the map can just look like this:

No functoids required, and it will give the exact same output as the above map. Do notice, that this only works with flat files where you make BizTalk suppress empty nodes.

So, that was all... feel free to ask questions

I hope this turns out to be helpful to someone.

--
eliasen