Snatched moments

BizTalk compile error with maps

2012-02-15T09:18:00.001+01:00

I am working with an integration against a SalesForce solution, where there is a very complex structure in SalesForce, where there are references that will never end in a recursive situation. This gives you an out of memory exception or your Visual Studio crashes.

The solution is to open the BizTalk map with an XML editor and change the (GenerateDefaultFixedNodes="Yes") to (GenerateDefaultFixedNodes="No") or (GenerateDefaultFixedNodes="RequiredDefaults"). If you have defaults in your map then you would need to take the last "RequiredDefaults" as you otherwise will miss those in your output.

Remember to test your mappings after you have done this change. See here for more information: http://msdn.microsoft.com/en-us/library/aa561485.aspx

Biztalk Server 2009 EDI parties, batches and deployment

2012-02-09T13:12:00.001+01:00

If you are using batches for EDI in BizTalk Server 2009 and earlier you might experience problems with deployment if you include your parties in your binding files or have a role link that have enlisted the party. If it is the later part you will always run into problems as you binding always includes parties even if you don't select it in your deployment.

The normal way to deploy would go into each party and stop the batch as this will stop the orchestration that always is running. You can see your running orchestrations in the BizTalk hub where it is possible to see which parties that have a batch running.

Well I ran into a case where it wasn't possible to see the batches in the BizTalk hub. Maybe someone terminated the orchestrations. And I had about 20 parties and with the slow BizTalk administration tool in BizTalk Server 2009 I wanted to find another way to find out which parties were blocking my deployment.

As always there is the SQL way to find the information. What you want to look at is the output from this SQL:

SELECT TOP 1000 [PartyId]

,[BatchOrchestrationId]

,[NumOccurences]

,[BatchId]

FROM [BizTalkMgmtDb].[dbo].[PAM_Batching_Log]

This gave my the following output:

And with the following SQL I could find the Party that was blocking by deployment:

SELECT TOP 1000 [nID]

,[nvcName]

,[nvcSignatureCert]

,[nvcSignatureCertHash]

,[nvcSID]

,[nvcCustomData]

,[DateModified]

FROM [BizTalkMgmtDb].[dbo].[bts_party]

With that information I could find the party in BizTalk and stop the batch. Some day I am going to find a way to start and stop edi batches automatically during deployment...

WCF, OneWay and BizTalk

2011-11-08T18:11:00.002+01:00

In WCF there is an option in your contract to markup as an OneWay method like this:

[OperationContract(IsOneWay=true)]

You will get the following error when you try to send a message from BizTalk to this WCF Service:

Details:"System.ServiceModel.CommunicationException: The server did not provide a meaningful reply; this might be caused by a contract mismatch, a premature session shutdown or an internal server error.

The problem with this is that BizTalk can't consume the WCF service as it only supports the Request/Reply pattern of WCF, so what options do you have when you have a fire-and-forget WCF service that you want to consume from BizTalk:

Change the attribute in your WCF Service, so the "IsOneWay" is set to "false". This is only an option if you have control over your WCF Service
Create a wrapper around the WCF Service that wraps you service. This blog post is one way to do it: http://www.pvle.be/2008/12/calling-one-way-wcf-service-with-biztalk-wcf-adapter-part-2/

You should go for the solution it the order listed above. Other possible solutions that I haven't tried, but might work (could be fun to try out at some point in time):

Implement a custom behavior (could maybe also be a solution as you hook into the WCF model, but I am not sure if it will work)
Implement your own WCF adapter using the WCF SDK (A lot of work, should work as you have complete control over the message flow)

WCF-SQL, BizTalk, notification and content based routing

2011-11-02T20:19:00.000+01:00

Many people use TypedPolling when they are working with BizTalk and wanting to poll data from the SQL Server and publish it to BizTalk, but there is another way where you use notification.

This sample show how to use notification with the WCF-SQL adapter and BizTalk. It shows that you can use notifications without orchestrations to call a stored procedure in a SQL Server database (Adventureworks) and send output from this stored procedure into the file system. The execution of the stored procedure is only done when BizTalk receives a notification from the SQL Server about something has changed in a table.

The first thing that is needed is that the SQL Server you are working with has SQL Broker installed and it is configured for the database that you want to poll data from:

The next thing to do is to create a notification schema in your Visual Studio BizTalk project:

This will give you this schema:

The schema has 3 elements:

Info --> contains the sql action that has been done on the table you are watching. Can be Insert, Update and more (http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlnotificationinfo.aspx)
Source --> contains the if it is data, object or something else that is the course for the notification: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlnotificationsource.aspx
Type --> contains the type of notification mostly change http://msdn.microsoft.com/en-us/library/z0fkxc6y.aspx

You should promote at least info and source element, so that you can use these fields for routing.

The next thing you need is to have a stored procedure that you can call to get the data from the SQL Server:

This procedure is just pretty simple and normally you would have some kind of logic to find out which rows that hasn't been polled from the table yet. It could be a datetime field, a boolean field or something else. There are plenty of examples of this on the net.

You then need to create a schema for this procedure, so that BizTalk knows how to call the procedure:

The next thing you need is a simple map to create the request message that is send to the wcf-sql adapter, so that it knows that you want to execute this procedure:

Now you just need to get the Visual Studio project build and deployed to the BizTalk Server.

The BizTalk Server now needs to be configured to use the schemas and map that we have created in Visual Studio. The first thing is to setup a Receive Port and Location (remember to set the pipeline to XMLReceive):

The 3 properties that you want to set are (there are also some that you need to connect to the SQL Server, but you most likely know how to do that):

The "InboundOperationType" needs to be set to "Notification"
The NotificationStatement must have a select statement for the table you want to get notifications on "SELECT [DepartmentID] FROM [HumanResources].[Department]" in my example
NotifyOnListenerStart must be True or False. If it is True it will give you a message when the receive port starts

The next thing you need is a request/response port where we send a request to the SQL Server that we want to execute a stored procedure:

Set the outbound map that we created:

Set the filter to route everything we get from the receive port (notification). Normally you would use the promoted properties in the notification schema to filter exactly when you want to poll and everything else in a garbage port (you always need a subscriber):

Now we just need a send port to subscribe to the output that the stored procedures returns. In this case it is just a file port, but it could be anything like a LOB system:

That is it. We have created content based routing based on the WCF-SQL adapters notifcation feature. The advantage is you get to poll the data from you SQL Server at the moment the insert/update and you don't need to poll for available data every 30 seconds even if there isn't any data. The disadvantage is that you will get some garbage notifications that you need to handle somehow and I don't think I would use this method on high tables with a lot of transactions as you will get a lot of notifications on every event.

Code available at: http://code.msdn.microsoft.com/WCF-SQL-BizTalk-notificatio-2d0d4b7e

WCF BAM interceptor and no data

2011-10-12T17:01:00.000+02:00

I had a situation where I were looking at using BAM (Business Activity Monitoring) from WCF (Windows Communcation Foundation) and I couldn’t find out why I didn’t get any data into my BAMPrimaryImport database. I didn’t get any errors so I was really wondering where my error was.

I launched the SQL Profiler to see if this could help me in my search for why I didn’t get any data. When the WCF service is launched it calls a stored procedure:

exec bam_Metadata_GetLatestInterceptorConfiguration @technologyName=N'WCF',@manifest=N'<namespace>.<interface>, <Assemblyname>, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null'

This stored procedure returned zero rows, so that was why I didn’t get any data into my tables in the BAMPrimaryImport database. I wasn’t sure why yet why this call didn’t return any data. I looked at the SQL code for the stored procedure and found that it queried the table “[bam_Metadata_EventSource]”. In this table it had the information that I was asking for, but the case of the <Assemblyname> in the manifest column where different than what I saw were used in the calling of the stored procedure.

So what I learned from this is to remember the correct spelling and casing in the IC file map:

<ic:EventSource Name="xxx" Technology ="WCF" Manifest ="<namespace>.<interface>, <assemblyname>, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null">

Something that might be hard to find…

BizTalk ESB Toolkit 2.1 installation guide

2011-10-06T13:46:00.000+02:00

In the past I never got the version 1.x installed because of a complex installation guide, so now that I am looking at the toolkit again I thought that I better write done some notes for myself (and maybe other), so that you can get it up and running.

My base image is:
- Windows Server 2008 R2
- SQL Server 2008 R2
- BizTalk Server 2010
- Visual Studio 2010
- and some other stuff that I don't think is relevant for the toolkit

First thing is to download the toolkit:
BizTalk ESB Toolkit 2.1

This also contains an installation guide, so what follows here is only the adds and special notes as I go through the installation guide.

Well you need some optional software from Microsoft before you can install it and the installation guide doesn't contains links:

Visual Studio 2010 SDK (If you get an error which tells you that Visual Studio 2010 is missing then you might have the wrong version of the SDK. There is one for each service pack. Otherwise it is a "next next" type of installation).

Windows SDK (Well this is a bit hard to predict what is needed. I don't want to install the full monty as this is 1,8GB. Here is what I choose. We will see later if that is enough. It is used for the portal. I would guess that it has something to do with the .Net framework as the portal is an ASP.Net web application).

Well for the chart control I think I will skip this because of the following statement (Beginning in .NET Framework 4, the Chart controls are part of the .NET Framework. ) From the sample page of the chart control.

If you are running in a domain and you use domain groups for the "BizTalk Server Administrators" and groups like this. Then remember to change those when you run the ESB Config toolkit.

If you get an error like this "Cannot open database..." when you run the BAM definition import. Then you need to add your SQL Server Analysis account as a datareader for the databases BAMPrimaryImport and BAMStar:

I had a lot of problems installing the ESB Management portal. I found out the reason was that I had Sharepoint Foundation installed on the Default Web Site. So what I ended up doing was delete the Sharepoint site incl. Default Web Site and then created a new Default Web Site for the portal. After some problems (read what it says in the installation guide carefully!!!) I managed to get it up and running. I had to do a couple of changes to the web.config file as my BizTalk groups are domain groups and I installed the core web service for itinerary and exception web service on a different web-site with a custom port (8084).

Well it looks like I am up and running. Time to do some testing...

BizTalk Server 2009 Deployment issue with multiple users on same server

2011-08-18T13:53:00.000+02:00

If you are more than one developer on the same BizTalk Server 2009 you might have noticed that there can be some deployment issues where you looses configuration that you or some other in your team already have done. Over time I have experienced the following issues from time to time:

One of the parties that I had enlisted on a rolelink where missing
The pipeline on a receive location where changed from XMLReceive to PassThrough
I couldn't deploy an assembly before I deleted a receive port. I got an error telling me that the transport type wasn't set on a receive location. But when I checked the configuration the transport type where set correctly.

The last one got me searching for why BizTalk does as it does.

I can't give you a reason why this happens, but I can give you a location that contains the binding files that BizTalk uses behind your back. The location is:

C:\Users\<Username>\AppData\Roaming\Microsoft\BizTalk Server\Deployment\BindingFiles

"Roaming" could also be something else based on how you logon to your server. I haven't gotten the issue on BizTalk Server 2010 yet, but that might be related to that I haven't work with another user on the same server with the same BizTalk artifacts or related artifacts. But I can see that it creates the same binding files as in BizTalk Server 2009, so my guess is that the same issue is also here in BizTalk Server 2010.

A common deployment procedure like using the Powershell privider for BizTalk would fix this issue, but I haven't gotten around to confirm this part.

Use single-signon with FTP, SAP and other username/password

2011-08-15T15:06:00.000+02:00

There are some of the adapters that doesn't support single-sign on with you domain users that are assigned to you BizTalk Host Instans. This is an issue in BizTalk when you come to deployment and where you don't want your password for a FTP site to be listed in your binding files.

When you configure your FTP Send Port in Biztalk there is an option to select the SSO Affiliate, which lists your SSO Affiliate applications from Enterprise Single Sign-On. This is a part of what is needed.

The first thing you need is to have a promoted property on your message called "SSOTicket" and this have to have a specifict value. I do this in a pipeline component, but I thing you also can do this in an Orchestration. The code is as follows:

public Microsoft.BizTalk.Message.Interop.IBaseMessage Execute(Microsoft.BizTalk.Component.Interop.IPipelineContext pc, Microsoft.BizTalk.Message.Interop.IBaseMessage inmsg)
{
ISSOTicket ssoTicket = new ISSOTicket();
inmsg.Context.Write("SSOTicket", "http://schemas.microsoft.com/BizTalk/2003/system-properties", ssoTicket.IssueTicket(0));
return inmsg;
}

Create a send pipeline that uses this pipeline component in the Encode phase.

Set the "Allow Tickets" to yes on the system:

Then you need to create an affiliate application in Enterprise Single Sign-On:

Give it a nice name and remember to check the check boxes:

Use the Biztalk Group that has the host instanse as Application users

Check "Ticket Allowed" and then finish the Wizard.

Click new mapping on the new application you just have created. Check the "Credentials as Windows credentials"

The Window user is the user used by the Biztalk Host Instanse and the External user is your FTP/SAP/Other user

Type in the password for your external user

Now on your FTP Send port you select the new SSO affiliate application.

This should be it. You now have single sign-on on your FTP sites and you can use your Enterprise Single Sign-on to manage your mappings between domain accounts and external accounts.

Sample code at http://code.msdn.microsoft.com/Use-single-signon-with-FTP-b6414ce8

Routing failure and request/respons send ports

2010-09-21T20:20:00.000+02:00

I had a very strange problem today. I had a one-way receive port that were using the WCF-SQL adapter. I used basic routing to route the message from this port to 2 different WCF-Basic-HTTP request/response ports. The problems was that I got a routing error on my receive port and where you normally would get 2 message I only got one message, which was the routing error. Normally you get 2 message one being the routing error and one being the message received. I went through alot of different things before finding out that you can only have one request/response port as a subcriber, which on some level makes sense, but I would have been nice to get some better error message.

If I change my WCF-SQL port to a simple File port I get the following message "The message found multiple request response subscriptions. A message can only be routed to a single request response subscription.", which made it pretty easy to correct the problem. I just have to save the message from the WCF-SQL port to the file system in 2 different folders and have some new ports based on these file locations. Not the nicest solution, but the only way when I didn't want to add an orchestration which might be another solution.

Bing map webservice

2010-01-26T20:19:00.000+01:00

In some solutions there is a need for some routing services, where your might want to show information to the user about how to get from A to B.
In the cloud there are some ways to do this. One is Google and one is Microsoft. This blog post looks at the web services that Microsoft makes available for us.
The first you need to do is to create a Bing Map Service account at the following link. As a developer you can have up to 5 different keys to use. If you have to use the services in commercials situations you have to contact someone at Microsoft to learn about the pricing and licensing issues. I think that Microsoft should do better at this point and at least show some pricing and licensing examples.

In the web services there is a couple of different services that you can use:

Geocode services, which can be used to lookup of longtitude and latitude of an address or do a reverse lookup of a longtitude and latitude to an address
Imagery services, which makes it possible to receive a map image that you might store in a database or something else
Route services, which can be used to create a route between two or more different points and where you can get the routing description in your local language. It doesn't have a optimizing feature, which could be nice in some solutions
Search services, which can be used to find companies and other stuff from in a city

There is an article on MSDN which makes it possible to get up an running with the webservices in a couple of hours. The only thing that might be a problem is that you get two endpoints for the same service, so you have to comment out one of the endpoints.

Besides the webservices there are also a Silverlight and Ajax controls that you can use. I haven't look into these at this point in time.
Overall it seems like nice services, where you can get up and running pretty fast. The only issue is that they don't list the pricing and licensing and that the services might have some more features.

Creating a new Matrix style in Performancepoint

2008-08-26T20:18:00.000+02:00

If you want to have a nice look for the matrixes that you create in Performancepoint you want to use the Matrix Styles as this gives you a nice and easy way to make sure that all the matrixes have the same formats. But what do you do when you don’t agree with the build-in styles? Say you would like to change the colors to better match your organisation.

It is pretty easy, but might also take some time to get just the right matrix style. The matrixes styles are based on the “PivotTable Style” in Excel 2007.

The first thing you need to do is to create a small PivotTable otherwise you wouldn’t get the right selects of formats that you want to change.

When you are standing in you PivotTable you have to use the “Format as Table” in the Home Ribbon and in the Styles section to change/create a PivotTable Style:

The best thing is to select one of the existing formats and select the duplicate function in the right click menu. Now you can change the styles you need to get the Matrix Style you want for your Performancepoint matrix with a little trial and error.

Create a NativeSQL Business Rule

2008-07-09T20:16:00.000+02:00

In some difficult situations you might want to use NativeSQL to implement a business rule. It gives you the full power of SQL Server.

First you need to create the rule in the Business Modeler to host the NativeSQL business rule. You can use parametres and even use members of dimensions as parameters. When it is time to execute the business rule the user gets a nice interface where the user is able to select the parameter in the dimension.

In the SQL it is the memberId of the dimension that is passed into the function and they are of the type int. A start of an implementation can be seen here:

create proc Calc.[sp$procedure] @entity int, @scenario int, @startTimePeriod int, @endTimePeriod int as

It looks almost as a normal stored procedure in SQL. The only difference is that the name is a preprocessor commands and the Performancepoint translate this to a real stored procedure name, when it is created by the Performancepoint in the SQL Server. The rest of the parametres is just like a normal stored procedure.

You implement your stored procedure just like you normally would with transactions, select, insert and delete statements. It can be a good idea to use SQL Server Management Studio to do some testing on the implementation before you add it to Performancepoint. In this implementation it is possible to use more preprocessor commands to reference different kind of columns.

When it is time to deploy you have to have the NativeSQL listed as a InActive rule for you to be able to deploy it with the Business Modeler. After this is done you have to have database access to change the bit field in the “RuleSetsOrRules” table from false to true. Then you have to go back to the Business Modeler to do a deployment of the rules again (after a refresh) and it at this time the the Performancepoint creates the stored procedure and if there are any errors in the sql like “Ambiguous column name” or “Invalid column name” they can be found in the Event Viewer. If all this goes well the Business rule is ready to be executed.

You can see the implementation of the Business Rule by expanding your stored procedures in you application database and check under the name “Calc.bsp_GeneratedCalc_[Businessmodel label]_Rule:[Business rule label][ModelId]”.

A small tip: Check out the the NativeSQL implementation of “Chart_CarryForward”, “Chart_GAP_Movement”, “Chart_Batch_GAP_Movement” and “Chart_Seeding” in the Financial Model without shares calculation for some tip on how NativeSQL can be used

Description field in the Time dimension in Performancepoint 2007

2008-05-08T20:14:00.000+02:00

What do you do when you want to use the description field in the time dimension in Performancepoint Server 2007?

The reason for this question is that I need to be able to show in an Excel input form which days are Easter each year, when is summer vacation and so on. They need this information to do better planning. The most logical answer would be to add this information to the Time dimension as it is information that is closely related to the dates in the Time dimension. You can't add a new field to the Time dimension, so I thought I use the description field which is already in the time dimension. The only problem is that you can't change the description field in the Planning Business Modeler.

I tried to change the table directly in the SQL Server and then did a Deploy Model Site in the Planning Business Modeler. You can see the result here:

This isn’t supported by Microsoft as you go and change something that isn’t change with the normal API or user interface.