How to create a null destination node in a BizTalk Map

This is the second time recently I came across this requirement so I guess others need it too. This post describes how to set a destintation node to null based upon a condition.

My situation was the source node contained a DateTime which was either valid or set to a zero length string. As some of you will know setting a date to a zero length string will actually set the date to 0001-01-01.  That is the date when the world began, allegedly.  My destination node is a date field too so I want to ignore the invalid date.  But here's the thing - I need to set the target node to null because I need the node present in the output.

The solution is to use a logical operator (= or <>) and a value mapping functoid.  In my case I am using a Script functoid to convert the source node value from DateTime to Date.  So the Script output is a date string.  By the way if you are wondering why I just don't test for this in the Script and set the return parameter to null, its becasue this doesn't work!

The <> functoid checks if the output is 0001-01-01 and sends true or false to the Value Mapping Functoid. As you can see I added 0001-01-01 as a constant for the second parameter.  Remember this is a Not Equal operator so it will return true if its a valid date and false if it is 0001-01-01. 

The magic happens in the Value Mapping functoid.  The description explains what it does. If the value of the first input parameter is true then the value of the second input parameter is returned.  It should add if the first input parameter is false, then the destination node is set to null. 

So all you have to do is select the correct logical operator (either = or <>) so that its output is true if you want to pass the value to the destination and false when you want to set it to null.  But be sure you have the parameters the right way round.  The logical operator needs to be at the top, the value of the source node is below.

Update 24/04/2012: I also ran into a problem where the source node was a Record, that did not contain data but held other elements.  I found that for the child elements the trick I used above worked well but, the parent element always appeared in the output as  < ParentNode />

I struggled for a long while to get rid of the offending  < ParentNode /> but in the end found that simply using the Logical Existence (?) functoid on the ParentNode was enough.  The result was the ParentNode was completely absent from the output except when any of the child elements contained data.

Dynamics CRM 2011 Date Picker Displays US format

This post will only help if you are using the On Premise version - I don't have a solution for the Online version. BTW, I was using SQL Server 2008 R2.

If  you add date parameters to a report that you want to run in Dynamics CRM 2011 then you get prompted to enter the dates. If you set the parameters as type DateTime then a date picker is also displayed.

But when you select a date, the picker puts the date in as US format.  It will still filter the data correctly but I wanted UK format displayed. 

The clue came from this blog.  Now in fact when I ran my report in Report Server (i.e. outside CRM) the date format displays correctly.
So I finally found the solution. You need to add Culture="en-GB" to the reportviewer.aspx page located at C:\Program Files\Microsoft Dynamics CRM\CRMWeb\CRMReports\rsviewer.  Note its the rsviewer directory not the viewer directory. I added it to the end of the first line - the <%@ Page declaration.  

Then the date displayed in UK format in CRM.

Filtered Fetch XML Reports for CRM 2011 On-line

I found very few blogs that explained how to create a Filtered Report for CRM 2011 using Fetch XML. My approach had been to use Advanced Find to create the Fetch XML and then copy and paste that into a Report in Business Intelligence Development Studio.  Now that works fine but when you publish the report to CRM it will only run on all records of the entity, it does not work on selected rows or on a single entity record.  The report just does not show up as selectable on the entity view of on the entity form. 

The missing attributes are on the the entity element of the Fetch XML.  Mine is a custom entity called new_identifiedneed and so I need to change
<entity name="new_identifiedneed">
to
<entity="new_identifiedneed" enableprefiltering="1" prefilterparametername="CRM_Filterednew_identifiedneed">

Now what is not made clear is that setting the correct prefilterparametername is crucial. It needs to be the name of the Filtered View for the entity prefixed by CRM_  (not CRMAF_ as for SQL based reports).

If you do this on the query  you will find that it will automatically add a parameter to your dataset. When you run the Query you will get this prompt for the parameter.

Don't bother with entering a value, just click OK and you should get unfiltered results. 
Check out the parameters on the DataSet which should look like this:

If that is present you're home free. Make the report look pretty, build it to create the rdl and then load it into CRM.  When you add the report be sure to set the Related Records (Identified Needs in my case) and select the Display in properties to include both the Form and the List.

Now go to the entity view and select on or more records. Click on the Run Report icon on the ribbon and you should see your report. When you run it on Selected Records you should see what you wanted.  The same applies when you open a form on a selected entity record. 

Happy Reporting!
BTW I found this out by using the Report Wizard for ad hoc reports in CRM. It will not only create the appropriate Fetch XML but gives you an RDL file that you can open up and play with in Business Intelligence Development Studio. 

Problems Configuring BizTalk 2010

I had two problems configuring BizTalk 2010 in a distributed environment I thought I would share.  The first was a problem on assigning the SSO Administrators group.  Since I was using a distributed environment I had to replace the local group name with the domain group name.  When I did so I got the warning icon with the detail.

Failed to add the user 'Domain\btsadmin' to the domain group 'Domain\SSO Administrators'

Now this user was already in the group and I was logged on as a Domain Admin so the message could not be correct.

There are blogs that say you need to re-register SSOSQL.DLL that is located in C:\Program Files\Common Files\Enterprise Single Sign-On   (and also in the sub-directory  \Win32) but I had done that.  The answer was: delete the existing entry and manually type in the account name -DON'T click on the ellpises (...). Stupid error I know, but that fixes it.

The second problem arose when configuring BAM.  I had installed the pre-requisites including SQLServer2005_NS_x64.MSI as well as installing SQL Server 2008 R2 Integration Services locally which is a requirement.  But I got the following error when applying the configuration

Could not load file or assembly 'Microsoft.SqlServer.Instapi, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.

Sure enough, this DLL was not in the GAC, although the 10.0 one was there.
Turns out you need to install the correct versions of the MSIs - I had somehow managed to download the wrong ones.  They correct ones are:

sqlncli_x64.msi                                            6430KB
SQLServer2005_NS_x64.msi                     3337KB
SQLServer2005_XMO_x64.msi               15066KB
sqlxml.msi 06                                             12683KB

They can be downloaded from here

Aftr that, the configuration went smoothly