CRM 2011 Attribute Lookup: Integrating BizTalk with CRM 2011

Hi Friends,
I have been working on a CRM 2011 Integration using WCF Adapter from BizTalk. Richard Seroter has done a very good job by posting 2 Articles on it which clears most of the basics.
Past few days I have been struggling to get this Lookup thing right. I tried the solution which Richard mentioned but seems he dint test it well...it fails.  I had also posted over msdn forum for help but nothing came up.
http://social.msdn.microsoft.com/Forums/en-US/biztalkgeneral/thread/3b08958a-cd8e-480d-9b35-eccbdfca04f8/#3b08958a-cd8e-480d-9b35-eccbdfca04f8

But this German link helped me a lot. I had to translate it to English but it was worth every penny.
http://www.msdynamics.de/viewtopic.php?f=60&t=12408

So what was the solution?
I had 2 Script functoids as shown in the map below as Richard has in the basic one first Series of CRM Integration.
http://seroter.wordpress.com/2011/02/10/the-good-bad-and-ugly-of-integrating-dynamics-crm-2011-and-biztalk-server-2010/

The C# Script in Functoid1 returns the name of the Key(Attribute Name) which is simple.

The important thing is mapping the value field where we lookup the other Entity which is shown as below

The Script is as below.
<xsl:template name="SetFieldValue"> <xslaram name="param1" /> <value xmlns="http://schemas.datacontract.org/2004/07/System.Collections.Generic" xmlnss="http://www.w3.org/2001/XMLSchema" xmlnssi="http://www.w3.org/2001/XMLSchema-instance" xmlns:a="http://schemas.microsoft.com/xrm/2011/Contracts"> <xsl:attribute name="xsi:type"> <xsl:value-of select="'a:EntityReference'" /> </xsl:attribute> <Id xmlns="http://schemas.microsoft.com/xrm/2011/Contracts" xmlns:ser="http://schemas.microsoft.com/2003/10/Serialization/"> <xsl:attribute name="xsi:type"> <xsl:value-of select="'ser:guid'" /> </xsl:attribute> <xsl:value-of select="$param1" /> </Id> <LogicalName xmlns="http://schemas.microsoft.com/xrm/2011/Contracts"> lucky_loadlist</LogicalName> <Name xmlns="http://schemas.microsoft.com/xrm/2011/Contracts" /> </value> </xsl:template>

This XML works fine against CRM
LogicalName is always the name of the Entity.
<ns0:Create xmlns:ns2="http://schemas.microsoft.com/2003/10/Serialization/Arrays" xmlns:ns0="http://schemas.microsoft.com/xrm/2011/Contracts/Services" xmlns:ns3="http://schemas.microsoft.com/xrm/2011/Metadata" xmlns:ns5="http://schemas.microsoft.com/2003/10/Serialization/" xmlns:ns1="http://schemas.datacontract.org/2004/07/System.Collections.Generic" xmlns:ns4="http://schemas.microsoft.com/xrm/2011/Contracts"> <ns0:entity><ns4:Attributes><ns4:KeyValuePairOfstringanyType> <ns1:key>lucky_accountsbylistid</ns1:key> <value xsi:type="a:EntityReference" xmlns="http://schemas.datacontract.org/2004/07/System.Collections.Generic" xmlnss="http://www.w3.org/2001/XMLSchema" xmlnssi="http://www.w3.org/2001/XMLSchema-instance" xmlns:a="http://schemas.microsoft.com/xrm/2011/Contracts"> <a:Id xsi:type="ser:guid" xmlns:ser="http://schemas.microsoft.com/2003/10/Serialization/"> 4B4B105F-6B82-E111-A667-00155D067401</a:Id><a:LogicalName>lucky_loadlist</a:LogicalName> <a:Name /> </value></ns4:KeyValuePairOfstringanyType> </ns4:Attributes><ns4:LogicalName>lucky_accountbylistvalidationrel</ns4:LogicalName></ns0:entity></ns0:Create>
So if you would like to work with CRM lookup its easy but hardly any resources online and strangely nothing from Microsoft on documentation regarding Integrating CRM 2011 with BizTalk.
I wasted good enough days banging my head to make this lookup work but its all is worth when you see it working finally.
Just mail me if you have any questions regarding Integration CRM 2011 using WCF Adapter in Biztalk.
Finally thanks Richard for post such valuable content regarding Integrating CRM 2011 using WCF BizTalk Adapter.

Hope this post helps you all!

Bm.exe is not a valid Win32 App

Hi,
Recently I had an Issue with BAM Bm.exe. I tried to run it through command prompt got a wierd error sayign its not a Valid Win32 Application. I thought its an issue since I installed BizTalk in 32 bit mode on a 64 Bit Windows 7 machine. On googling found nothing ..seems nobody encountered it ever. Finally I thought google wont help looked at bm.exe and found the file size was 0.
Seems it got corrupted somehow. By replacing it with a new bm.exe solved the problem.

So basics do help when google fails to help you.

Exposed Orchestration as Web service returning void?

Hi,
I was working on an Integration Scenario where we needed to call the BizTalk Orchestration from MS Access.
SO first thing started looking at how could we could send request from MS Access to Biztalk. We wanted a Synchronous Scenario where MS Access sends Request and waits for a Response.
The best option we had was to Expose the Orchestration as Webservice.
We did that and after googling we understood

•  MS Access cannot consume a WCF Service.
• To Comsume a BizTalk WCF Service we need to create a Proxy Class.

So finally we thought to settle down for normal SOAP Web-services and exposed our Orchestration as Soap Web-service.

It looked good but we planned to make it more easy to be called from MS Access we thought to have the Web-service Input/Output parameter as string.

So MS Access will send XML in string and get response XML i nstring object.

So we made the changes in Orchestration and republished the Orchestration as Webservice and tried to consume it from a WebService testing tool caleld WCF Strom.

The input was passed on and the Webservice returned void though in BizTalk I noticed that I am returning the right XML.

After googling I found this link where it says we need to fine tune the Webservice to work it out.

http://stackoverflow.com/questions/6627798/biztalk-web-service-to-return-a-value-rather-than-void-ref

I made the changes in my Webservice created by the Webservice Publishing Wizard.

And finally got the Response as string.

Hope it helps you in working with Exposing Orchestration as Webservices.

Message as Mail Attachment?

How to send any message as a mail attachment?

1. No need to create Multipart messages.
2. No need to set any parameters.

Just set the Dynamic SendPort SMTP properties like.

UserName,Password,Hostname,To email address.

You can find more settings here

http://geekswithblogs.net/mapfel/archive/2006/11/24/98761.aspx

Important thing is using the Mime Encoder in the Send Pipeline if you want to send attachments.

It seems like I tried setting the property in Orchestration as
msgXmlDocumentOut(SMTP.MessagePartsAttachments) = 1;

It dint work.Finally I added the PipelineComponent and it worked fine. You can also set the FileName Overriding the Mime.FileName property as shown below.

public static void SetMimeFileName(Microsoft.XLANGs.BaseTypes.XLANGPart msgpart, string MimeFileName)

            {

                msgpart.SetPartProperty(typeof(MIME.FileName), MimeFileName);

            }

Global Context Field which is independent of a Message

I was thinking of writing about a Context field which is independent of message. Imagine a Scenario where you would like to promote a field only for routing no matter what the incoming file content is, you know one thing for sure that there is a field in the content of the file(FF,XML,EDI) which has the data for routing to destination system. In this case it never makes sense to create a Schema and promote the field as Distinguished or Property field and only for routing you don’t even need to create an Orchestration.Message In….. MessageOut ….simple J. 

So I thought to show you how simple is it to do with BizTalk.

1. Create a new BizTalk project and add a new Property Schema which will have our Global PropertySchema field.
2. Let’s add a field called Property1 to this Property Schema. Set the PropertySchemaBase as MessageContextPropertybase. So by this we say that the field Property1 will not be dependent upon the message content or it won’t be linked to any field in the message. 
3. This namespace below is important. This is the namespace in which this property field (Property1) will be promoted globally.
4. Deploy the solution to BizTalk so our global property Schema is in BizTalk.
5. You can promote this field from any pipeline or Orchestration as shown below. pInMsg.Context.Promote(“Property1”,"https://BizTalk_Server_Project3.PropertySchema1", "Test");
6. If you want to access it from Orchestration then it’s important to know the .Net Namespace the property belongs to as shown below in the properties.

The Property field (Property1) can easily be accessed from the Orchestration with the .Net namespace. strProp=Msg_XmlDocumentIn(BizTalk_Server_Project3.Property1);

Simple enough…promotions are important as they can save lot of your design time to plan complex Integration's. Promotions can help you route any message from a ReceivePort to SendPort but I limit the use of such uncontrolled scenarios and use Orchestration as it gives me full control over the message for complex scenarios… ;) .

SQL Receive Adapter: Multiple tables in different Database

Last week I had a simple scenario where BizTalk Server will use a SQL Receive Adapter and poll a SQL Stored Procedure(SP) which will return the rows from multiple tables spread across 2 different database.
Initially I thought the only catch out here will be to grant rights across all tables for the same User Account with which Biztalk polls the SP. Following were the last 2 lines of my proc.
SET @ReturnXML=SELECT  * FROM ABC INNER JOIN DB2.dbo.XYZ as XYZ ON ABC.FIELD1=XYZ.FILED2 WHERE ABC.UpdateID=@UpdateID FOR XML AUTO,ELEMENTS
Select @ReturnXML
The SP worked perfect from SQL Query window but I started getting the following error when called by BizTalk SQL Receive Adapter.
Event ID: 5740
Description:The adapter "SQL" raised an error message. Details "HRESULT="0x      7a" Description="Description not provided"
I thought I am missing some access rights on the 'XYZ' table, I granted all possible rights for the user on the table but still the same issue. After struggling for 2 days I thought to concentrate only on what the SP returned in the last 2 lines shown above.I felt that BizTalk is having an issue with the data returned from SP as it wont care what you do in the SP.BizTalk is only interested in the results(rows) returned,So I removed the variable(@ReturnXML) and it worked. So SP now returns the SQL Query directly.
SELECT  * FROM ABC INNER JOIN DB2.dbo.XYZ as XYZ ON ABC.FIELD1=XYZ.FILED2 WHERE ABC.UpdateID=@UpdateID FOR XML AUTO,ELEMENTS

Hope it helps you.

BAM Portal Activity Search: "This action cannot be performed because one or more database(s) appears corrupted"

Hi,

I noticed this error when I tried to search on an Activity in the BAM Portal.

I googled and noticed a good description about this issue by Gilles but it did not help me resolve the problem.

http://blogs.msdn.com/b/gzunino/archive/2006/07/12/663790.aspx

So I thought of fixing it in the BAM (BAMPrimaryImport)database as it clearly said about duplicate entry. I searched on the ActivityID and found only 1 record in "bam_ActivityName_active" table and I also found 1 record in the "bam_ActivityName_completed" Activity table.
SELECT * FROM [BAMPrimaryImport].dbo.bam_ActivityName_active where ActivityID='{42B99A00-4A43-40D4-8F6F-183851984197}'

So I thought that its not a duplicate issue as the Error says as I saw only 1 entry in each tables.

But I was wrong.

The issue is that there should not be any pending records in the Active tables if  the Activity is completed ie. if there exist a record in _Completed table for the same ActivityID.

In my case I intiated an Activity(BeginActivity) and Biztalk failed while updating the Actvity(UpdateActivity)and after that I completed the Activity(EndActivity). So there was an Orphan record in the _active table.

So I deleted all the records from _active table and it worked fine.

DELETE FROM [BAMPrimaryImport].dbo.bam_ActivityName_active

So this is what BizTalk means by Duplicate records.

"There should not be a record in the _active table if the activity has a record in the _completed table for the same ActivityID.If it exist then its a duplicate Error."

Error in Eventlog was as follows:
Event Type: Error
Event Source: BAM Portal
Description:
(BAMPortal.PortalApplication) Void LogAllErrors(System.Exception[]): System.Web.HttpUnhandledException: Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> System.Web.Services.Protocols.SoapException: System.Web.Services.Protocols.SoapException: There are instances with duplicate ID '{42B99A00-4A43-40D4-8F6F-183851984197}' in activity 'xxx'. The duplicates must be removed from the database to fix this problem.
   at Microsoft.BizTalk.Bam.WebServices.Query.BamQueryService.GetInstanceData(String viewName, String activityName, InstanceQuery query, Int32 timeout)
   at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
   at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
   at Microsoft.BizTalk.Bam.WebServices.QueryService.BamQueryService.GetInstanceData(String viewName, String activityName, InstanceQuery query, Int32 timeout)
   at Microsoft.BizTalk.Bam.Portal.DataAccess.QueryResultsObjectDataSource.ExecuteQuery(SQLQuery query)
   at Microsoft.BizTalk.Bam.Portal.DataAccess.QueryResultsObjectDataSource.EnsureData(SQLQuery query)
   at Microsoft.BizTalk.Bam.Portal.DataAccess.QueryResultsObjectDataSource.QueryResults(SQLQuery query)
   at BAMPortal.Search_aspx.ExecuteQuery()
   at BAMPortal.Search_aspx.executeQueryButton_Click(Object sender, CommandEventArgs e)
   at System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e)
   at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
   at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
   at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
   at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
   at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
   --- End of inner exception stack trace ---
   at System.Web.UI.Page.HandleError(Exception e)
   at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
   at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
   at System.Web.UI.Page.ProcessRequest()
   at System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context)
   at System.Web.UI.Page.ProcessRequest(HttpContext context)
   at ASP.pages_search_aspx.ProcessRequest(HttpContext context)
   at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
   at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)


Happy Talking Biz. ;)