WCF SQL operations

When adding WCF sql ports to BizTalk, you can add a whole host of different types of operation with SQL and it’s incredibly powerful. 

The problem is with all these options, the wizard rarely makes a binding file for you, it will make the schemas, but the binding file contains that extra little bit of magic…

The key to all of this in the SOAP action header you specify on the port.

There are your basic ones:

TableOp/Insert/dbo/Employee

TableOp/Update/dbo/Employee

If you read this, it will do a table operation and insert or update employee record(s) in the employee table.

Then there is of course stored procedures for both send and receive ports. 

TypedProcedure/dbo/StoredProcedureName

Simple enough, however maybe you have a few operations you want to do, you want to go for a more composite operation. 

Your Soap action can be CompositeOperation

The message you send can do a whole host of things, for example, insert into a table, and when done, run a stored procedure.

You have your insert schema, and your execute stored procedure schema, that the wizard generates, now bundle this into the following structure and all of a sudden a whole world of opportunities opens up.

<xs:element name="Request">

    <xs:complexType>

      <xs:sequence>

        <xs:element ref="ns0:Insert" />

        <xs:element ref="ns1:CompareEmployee" />

      </xs:sequence>

    </xs:complexType>

  </xs:element>

  <xs:element name="RequestResponse">

   <xs:complexType>

    <xs:sequence>

     <xs:element ref="ns0:InsertResponse" />

     <xs:element ref="ns1:CompareEmployeeResponse"/>

    </xs:sequence>

   </xs:complexType>

  </xs:element>

Then there are the Generic operations, vastly undocumented, for example a SQL reader, that can execute SQL against the database and return the results of the query…

Your Soap Action header would be: GenericOp/ExecuteReader

The schema looks like this:

  <ExecuteReader xmlns="http://schemas.microsoft.com/Sql/2008/05/GenericTableOp/">

    <Query>[PL/SQL STATEMENT1];[PL/SQL STATEMENT2];…</Query>

  </ExecuteReader>

Have a look at some of the more unknown options available, and you start to see the power in all of this. 

Operation	Soap Action  Header
ExecuteNonQuery Request	GenericOp/ExecuteNonQuery
ExecuteNonQuery Response	GenericOp/ExecuteNonQuery/response
ExecuteReader Request	GenericOp/ExecuteReader
ExecuteReader Response	GenericOp/ExecuteReader/response
ExecuteScalar Request	GenericOp/ExecuteScalar
ExecuteScalar Response	GenericOp/ExecuteScalar/response

Refer to this link for more: http://msdn.microsoft.com/en-us/library/dd788372%28v=bts.10%29.aspx

Accessing the SharePoint user profile service from BizTalk

I have a client scenario where weird 3^rd party system 1, sends me an update of some information, from time to time, about a user, this information needs to be instantly replicated into SharePoint.

I could use BCS but it’s not instant…  It’s coming to me via BizTalk, so I have it in near real time, so 

I can update SharePoint can’t I??… well yes it seems you can…

There is the web service that SharePoint quite nicely exposes:

http://%7bserver%7d/_vti_bin/userprofileservice.asmx

It has many methods one of these is: ModifyUserPropertyByAccountName

It’s a one way send in BizTalk. 

When you add a reference, you need to add a generated item, that consumes a WCF Service.

You will get 2 schema s an orchestration and a port binding, the schema s are useful, the orchestration you can choose to use it or not, it contains a bunch of multi-part message types,  and a massive port for every single method.

The port is useful, because it contains the operations, you will need to use when you create the port, so one port can have many operations to the same web service, they give you the binding for this port, which is very nice, I suggest you use it.

Here is where all the nice stuff ends, and the really interesting stuff begins. If you use this, it does not work. 

You may get:

Error details: System.ServiceModel.FaultException: <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soap:Body><soap:Fault><faultcode>soap:Server</faultcode><faultstring>Server was unable to process request. ---&gt; Invalid String Value: Input must match string data type.</faultstring><detail /></soap:Fault></soap:Body></soap:Envelope>

This made no sense to me, as I was sending a string, and updating a string…..

If you look at the schema for the message:

value is defined as xs:anyType, which means you can put anything in here, which is correct, I want to, a string, a datetime, a int. So the schema is flexible…

The wsdl is very vague:

      <s:complexType name="ValueData">

        <s:sequence>

          <s:element minOccurs="0" maxOccurs="1" name="Value" />

        </s:sequence>

      </s:complexType>

The value is does not specify xs:anytype however because it does not specify a type BizTalk interprets this, bad sharepoint.

However it does not go far enough… and hence it is a real let down when you have gotten past the authentication issue to find this. See my other post to fix this.

If you call the method from .net code, it works… however to find out why it works, you look at the xml that this call generates:

<ValueData>

   <Value xsi:type="xsd:dateTime">2012-06-25T10:01:17.486123+10:00</Value>   </ValueData>

The “value”  has an uppercase V, BizTalk puts is with a lower case V.  so that’s the first fix.

The next problem: xsi:type ??

It’s not even in the schema, it’s not in the wsdl.  I try and try and try to get it in to the schema, it’s not going to happen.

I managed to add an attribute to the value, called type, however it’s coming up as:

<ns0:Value ns0:type

Now the values are being set in SharePoint, all to NULL because it cannot interpret the type.

Now I know what I need to make the message look like, I have BizTalk pipelines… I can touch up the message before I send it to SharePoint.

So I go and touch up the message and the thing works perfectly. !!

I can now communicate from BizTalk to SharePoint, to update the user profiles.

My port looked like this:

Accessing an authenticated web service using BizTalk and impersonation

I need to access the SharePoint user profile service.

SharePoint quite nicely exposes:  http://{Server}/_vti_bin/userprofileservice.asmx

It is authenticated, quite rightly so, however the user I am accessing it with has permissions, however it still asks for username and password.

I was getting this error:

The adapter failed to transmit message going to send port "WcfSendPort_UserProfileServiceSoapOneWay" with URL "http://{Server}/_vti_bin/userprofileservice.asmx". It will be retransmitted after the retry interval specified for this Send Port. Details:"System.ServiceModel.FaultException: <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soap:Body><soap:Fault><faultcode>soap:Server</faultcode><faultstring>Server was unable to process request. ---&gt; System.ServiceModel.EndpointNotFoundException</faultstring><detail /></soap:Fault></soap:Body></soap:Envelope>

Server was unable to process request. System.ServiceModel.EndpointNotFoundException

Not much help to me…

After some searching I found I needed to use impersonation.

The main reason was from .net I can simply do:

WSService.Credentials = System.Net.CredentialCache.DefaultCredentials;

Now the current user is impersonated and if they have permissions it works. GREAT.

I’m in BizTalk, and I’m on the WCF port…

The port is using wcf-basichttp as the protocol, and it should be able to just I have no idea how.

Well the solution I found is rather simple.

Use a wcf-custom port.  

Setup the basic-httpbinding.

First of all set the transport to client credential type ntlm 

 

Then flip over to Behavior

It will be blank.  Right click end point behavior: 

Select add extension.

Add client credentials and configure like so:

 After doing this my error went away.

BizTalk vNext features or WF/WCF vnext features?

Look at the next version of BizTalk 2009 R2, coming soon, and then think what else would I like to see...

Then understand that the team that wrote BizTalk is the same team that made WF and WCF.

I'd turn it around to say, look at WF and WCF where the bulk of the effort is. What would I like to see here, as post Dublin we finally have a product that works, with nice hosting and manageability. This is really the way to go. .

So ask what you would like to see more of in this stack. As it's still not 100% usable, and very clunky in areas here BizTalk is seamless.

I'd like to see a mapper that works for WCF endpoints. Defined for contracts, to map incoming formats into that of the contract.

This would involve identifying the incoming format first, like matching it to a different contract/schema, for the map, and then applying the transformation. Sounds a lot like a BizTalk port.

Then mapping inside of WF, to construct an outgoing message from a different incoming format.

WF is far tooo clunky for this...

You will see more ws-* wcf adapters, however the other features, like debug orch in vs, never going to happen. It's already there in wf so why put it in BizTalk....

I've been waiting for that new mapper I saw 3 years ago to appear in BizTalk, still no sign of it....

Where does Dublin, WF and WCF fit with BizTak going forward?

I previously spoke about where BizTalk and Dublin (Insert Name), WF and WCF all fit…. Well here is my view.

As far as where does Dublin fit here I can only touch on this, Dublin could host the workflow, much like BizTalk hosts the orchestration and the communication to end points. Dublin workflows could call BizTalk to kick off the back end communication and orchestration process, and get a result when they are done. In this way the workflow/human workflow can interact with back end systems, in a correctly architected manner, you can of course cut the corners here and call wcf services hosted, not a good idea, you could call back end oriented workflows that would be hosed in Dublin. WF can’t talk to SharePoint, and it perhaps can’t talk to many back end systems, whose functionality live in BizTalk. For example WF can’t send a fax.

There are a few fax adapters that can. WF can’t map a document from one format to the format that the end system is expecting; it has no concept of this. WCF can’t do this, and WF can’t do this. BizTalk will be here for few more years still.