Select * from Salesforce using (SOQL) REST Api and BizTalk 2013 R2

Posted on Updated on

Quite recently I was working for a project that needed Salesforce data from cloud back to on premise SQL.

Salesforce Rest Apis enable working with Queries and a SOQL query can be executed under Query resource that returns all the results in a single response, or if needed, returns part of the results and an identifier used to retrieve the remaining results.

Rest Api Call: https://yourInstance.salesforce.com/services/data/v20.0/query/?q=SELECT+name+from+Account -H “Authorization: Bearer token”

As we can see there the SOQL needs to have the field name that we want and a select * is not supported by SOQL.

So the requirement was to fetch all the data, that could have been achieved by specifying all the field names on Query but then the solution would be dependent on Salesforce updates and modification.

The next approach is to query the available fields from Salesforce and dynamically create the query to execute.

Rest Api :

https://yourInstance.salesforce.com/services/data/v20.0/ sobjects/Account/describe

The resultant xml is like:

QueryResultXmlCapture

Now we can iterate over all the <fields> and get all field names for our SOQL query.

C#Capture.JPG

And then build the “Select GetAllFieldsQuery(describe Xml) from Account”

This way for any changes to Salesforce object, the interface doesnot need to change and is not prone to breaks or unexpected result because the results are laways in sync on Salesforce cloud and On premise SQL.

The Rest Api calls are easily done via BizTalk 2013R2 Web-Http adapter. The default response for Api calls is Json and BizTalk 2013R2 is fully equiped with tools to handle Json messages. If however, we intend to have the response as xml, this can be achived by setting Http Header – Accept : application/xml

AdapterPropertiy

A comprehensive Rest Api documentation for Salesforce API_REST.pdf

404 – File or Directory not found, BizTalk WCF calls to service

Posted on

Quite recently we encountered an error in BizTalk WCF sends when the message being sent over the wire.

The error being reported on BizTalk and event viewers is more of a generic error like:

Size Issue

We noticed that the error happened wheever the message being sent is big in size say 100 MB- 150 MB.

So to check if this was infact the issue, we increased the maxRequestLength=”2147483647″ which did not help.

For IIS 7 and above there are more settings to be modified for this to work, there needs to be a security settings in the config that allows the service to accept bigger messages.

<system.webServer>
<security>
<requestFiltering>
<requestLimits maxAllowedContentLength=”1048576000″/> <!– 1000 MB –>
</requestFiltering>
</security>
</system.webServer>

The setting can be applied at the IIS level as well and that would then apply to all the services hosted on the IIS server.

Salesforce disabling TLS 1.0 – How to get it working for API calls via BizTalk

Posted on

Salesforce will soon be disabling TLS 1.0 support.

Starting in June 2016, Salesforce will begin disabling the TLS 1.0 encryption protocol in a phased approach across impacted Salesforce services. The disablement of TLS 1.0 will prevent it from being used to access the Salesforce service within inbound and outbound connections.

More Details

In Continuation to the great post on how to call salesforce APIs via BizTalk , the WCF behaviour can be extended to inforce any other TLS protocol.

ApplyClientBehaviour method can be modified to apply security protocol on the outgoing messages. By default this is SSL 1.0

TLSBehavoir

You can pass on the parameter from the configuration like the other params.

SEcurityProtocol

 

WCF Behaviour extension would look something like.

BizTalkConfigTLS

 

BizTalk Application Deployment Tool – The BAD Tool

Posted on Updated on

http://biztalkapplicationdeployment.codeplex.com/

The tool is built for automating your BizTalk applications deployments across multiple servers using the native BizTalk built MSIs that is generated from BizTalk Admin console.

The deployment tool frees you from hassles of scripting/ multiple server logons to run scripts etc. You get a single UI from where all your actions can be executed giving you a complete picture of the deployment steps.
The tool uses library WindowsInstaller from http://wix.codeplex.com/ to get Msi info.

The tool is intuitive and various features in the tool are easy to use. Please use the tool and post your comments for any questions/ wishes etc..

Features
BizTalk Application Deployment tool has following main features :

  • Single click multi server deployment of Msi
  • Complete view of all the actions being performed during the deployment process on screen
  • Use the standard BizTalk generated Msi for deployments
  • Has a bunch of productivity tools to assit during deployment and administration
  • HostInstances feature assists you in quick host instance operations
  • Deployment Health checks
  • Artifact Inspector helps you deeper view of the application’s artifacts such as schemas, maps etc
  • Msi inspector helps you quicly inspect your Msi for resources info
  • IIS deployment features are also included in the tool that would help you automate IIS related operations across multiple servers in the farm
    • Manage Application pools : Create, Recycle, Delete
    • Manage Applications : Create, Delete, Change application pool
    • Perform health checks of your biztalk web service
  • BRE deployment tool that would save you from the cumbersome Bre deployments
  • Assembly Gac feature that would facilitate GAC of multiple assemblies across multiple farm servers from UI and single click.
  • Easy to use and is intuitive

Exception Uploading files > 2MB to Sharepoint using BizTalk 2013 CSOM

Posted on

The other day I was working on an application that would be uploading files to SharePoint 2013 using BizTalk 2013.

With BizTalk 2013, Microsoft has introduced CSOM (Client Side Object Model) and as a result of which there no need to install the SharePoint Service that was required till BizTalk 2010.

Everything seems to be working fine till I tried uploading a file of size 1.8 MB and BizTalk failed to upload the file with below exception.

image001

So the error is clearly the server exception and SharePoint is not allowing files of size > 2097152 byes or approx 2MB.

There is one interesting stuff to check here.. The file size being uploaded is 1.8 MB and allowed size is 2 MB so why is it failing ??

After digging on to SharePoint logs I could see the content of the file and voila the content is a Base64 string. So this clarified the reason as Base64 string is greater than the original data so a 1.8MB data could well grow > 2MB after Base64 conversion..

But still a 2MB limitation is too much and it should not be like this.

There is a setting in SharePoint where you can increase this size limit. It can be done using a power shell script.

Add-PSSnapin Microsoft.Sharepoint.Powershell

$service = [Microsoft.Sharepoint.Administration.SPWebService]::ContentService

$service.ClientRequestServiceSettings.MaxParseMessageSize 2147483647

$service.ClientRequestServiceSettings.MaxReceivedMessageSize 2147483647

$service.Update()

Using the above script the size can be increased to 2 GB and after running the script everything went fine and files of size 200, 250 MB were getting successfully uploaded.

Note : Its the MaxParseMessageSize that actually does the trick 🙂

 

%BTAD_InstallDir% not working in AddResource Command (BTSTask.exe)

Posted on Updated on

The global environment variable %BTAD_InstallDir% does not translate as you would expect when you AddResource using BTSTask.exe

in a batch command or in build event of assemblies.

A command such as :

BTSTask AddResource -Source:”$(TargetPath)” -ApplicationName:AppName -Type:System.BizTalk:Assembly -Overwrite  -Destination:”%BTAD_InstallDir%\$(TargetFileName)” -Options:GacOnInstall

is executed successfully but when you see the same in BizTalk admin console you find that its translates only to \$(TargetFileName) and the environment variable is skipped.

MSDN link suggests the same but that does not work.

Capture

 

To make this working use the below.

BTSTask AddResource -Source:”$(TargetPath)” -ApplicationName:AppName -Type:System.BizTalk:Assembly -Overwrite  -Destination:”%%BTAD_InstallDir%%\$(TargetFileName)” -Options:GacOnInstall

Note the two % symbols. This is because single % is skipped in evaluation.

 

BizTalk 2013 Map Issue and Resolution (.NET XSLCompiledTransform omen)

Posted on

The other day I was doing migration of some of the application from BizTalk 2010 to BizTalk 2013 and after successful migration, there was an issue while testing few maps. The same map which worked perfectly as expected in 2010 were not so happy in 2013.

After looking through the same the issue was found. Went on though lot search and reads, found some interesting news.

There has been a drastic change in how maps are executed in BizTalk 2013. With the new version on BizTalk Microsoft has introduced .NET XSLCompiledTransform in place of .NET XSLTransform for the mspping engine.

With this in, there has been performance improvement and memory utilization improvements.

But this major change in the product has potential, to make your existing old version maps fail under certain circumstances.

The cases being :

1. If the map is using custom scripting functoid which has boolean parameter. Please refer HERE.

2. If the map has Extension Objects and Script Functions with certain implementation. Please refer HERE.

There is a great post here explaining the details. 

As explained in the KB article.:

For example, consider the following code within a scripting functoid:

public int AddIfTrue(int param1, int param2, bool addNum)
{
    if (addNum) return param1+param2;
    else return param1;
}

In BizTalk Server 2013, the behavior is as follows:

  • If addNum is true, false, or any other value the output is param1+param2
  • If addNum is empty the output is param1

In prior versions of BizTalk, the behavior was as follows:   

  • If addNum is false the output is param1
  • If addNum is true, the output is param1+param2
  • If addNum is empty or any other value the functoid fails with error “String was not recognized as a valid Boolean”

In order to revert to the previous behavior, the scripting functoid code can be modified to take a String parameter instead of Boolean and then convert the String to Boolean within the functoid code as follows:

public int AddIfTrue(int param1, int param2, string addNum)
{
    bool addNumBool = System.Convert.ToBoolean(addNum);
    if (addNumBool) return param1+param2;
    else return param1;
}

It is also possible to configured the BizTalk 2013 Transform Engine to use the older XSLTransform class. This approach is not recommended since the environment will lose the many performance and memory usage improvements provided by the XSLCompiledTransform class. This change can be made by adding DWORD UseXslTransform with value 1 at the following locations:   

  • For 64 bit BizTalk host instances: HKLM\SOFTWARE\Microsoft\BizTalk Server\3.0\Configuration
  • For 32 bit BizTalk host instances and Visual Studio’s Test Map functionality: HKLM\SOFTWARE\Wow6432Node\Microsoft\BizTalk Server\3.0\Configuration

So, we just need to keep an eye on this when we are doing migrations.