When the Oracle E-Business Suite (EBS) Adapter performs an operation on an Interface Table, Interface View, Concurrent Program, or a Request Set (Coming in the CTP4), it compulsorily sets the application context. To set application context, the adapter calls the FND_GLOBAL.APPS_INITIALIZE API. However, to call this API, the adapter requires three values:
1. User Id
2. Application Id
3. Responsibility Id corresponding to the User Id and Application Id.
The adapter gets the Responsibility Name from the binding, EBS User Name from the binding (or from Client Credentials starting CTP4), and retrieves corresponding id's from the backend to use when initializing application context. As I mentioned, it also needs the application id. For Interface Tables, Interface Views, Concurrent Programs, and Request Sets, the action contains the application short name. The adapter uses this application short name and ignores the value in the binding property ApplicationShortName to get the application id from the back end.
So when is the ApplicationShortName in the binding property used? It is used for setting application context for artifacts which do not have the application short name in their action. This includes PL/SQL APIs, Procedures, Functions, DB Tables, and DB Views. The adapter does not 'know' which application these artifacts belong to (if they belong to one). The adapter does not enforce setting application context for these artifacts, but you can make the adapter set the application context for them by proving the EBS User Name as well as the application short name.
When the adapter sees that the operation is to be performed on a PL/SQL API, Procedure, Function, DB Table, or DB View, if the ApplicationShortName in the binding and the EBS User Name are available, it goes ahead and tries to set the application context. Of course - it requires a valid Responsibility Name in order to be successful.
When (and only when) the application context is being set, the adapter looks at the binding property OracleEBSOrganizationId. If this has been populated, the adapter sets the Organization Id, else it does not.
With CTP4, which is due in some time, we support specifying ApplicationShortName, ResponsibilityName, and OrganizationId using Message Context Properties which provides greater flexibility in choosing responsibility and Org Id, and even override the application short name in the action. That will be the topic of my next post.
Till then, Happy Coding!
In this post, I outlined the ways in which stored procedures can be executed using the SQL Adapter. Similar to the methods described there, are very similar methods for polling on the inbound side. (I won’t mention the advantages/limitations/workarounds here, since there are very similar to those mentioned in the other post).
1. The first method for polling, manifested as the “Polling” operation in the Metadata Search/Browse tool. At runtime, the SQL statement specified in the “PollingStatement” binding property gets executed, and the result sets returned (we support multiple result sets being returned) are surfaced as System.Data.DataSet[] (an array of datasets).
NOTE – in order to let the adapter know that you want it to perform this operation at runtime, set the InboundOperationType binding property to Polling.
2. The next approach was to try and expose the result sets as “strongly typed”. For this purpose, we added the “TypedPolling” operation which you can see in the Metadata UI. Metadata is obtained (at design time + runtime) using ADO.NET’s CommandBehavior::SchemaOnly option (which translates to SET FMTONLY ON).
NOTE – in order to generate the metadata, setting the InboundId property in the Connection Uri is mandatory – since in a single BizTalk application, you can have multiple receive locations with different PollingStatement values, and in order to differentiate the XSDs, the InboundId value is used (thus, the InboundId should be different for different Receive Locations)
NOTE – in order to let the adapter know that you want it to perform this operation at runtime, set the InboundOperationType binding property to TypedPolling.
3. In CTP4 (releasing end of October 2008), we’ve added one more mechanism. This was mainly done for backward compatibility.
In this method, the PollingStatement you specify should return data in XML format (e.g., SELECT Col1 FROM Table1 FOR XML AUTO). At runtime, the XML data returned is enclosed within the root tag defined by the values in the binding properties XmlStoredProcedureRootNodeName and XmlStoredProcedureRootNodeNamespace.
NOTE – in order to let the adapter know that you want it to perform this operation at runtime, set the InboundOperationType binding property to XmlPolling.
A question which has popped up quite a few times over the last couple of weeks – “For an outbound contract, I see RFC, TRFC, IDOC and BAPI nodes in the UI. However, for an inbound contract, the BAPI node is missing. Why?”
Firstly, I’m going to explain what each of the four nodes you see really translate to within the adapter, and how they are “transported” to SAP.
The adapter communicates with SAP using the RFC protocol (it uses the RFC SDK for this purpose – which is written in C). RFC stands for “Remote Function Call” – a mechanism for calling Function Modules (functions) on SAP from external systems (you can also have functions defined on SAP which are not enabled for remote access). Using the RFC SDK, an external client can either invoke a RFC on SAP (outbound calls), or listen for incoming function calls (inbound).
The RFC and TRFC nodes you see in the metadata UI, are the only *pure* manifestations of this in the adapter.
Under the RFC node, we display all the function modules (which have been configured on SAP to allow them to be called from an external client). Invoking an operation under this node – the adapter just turns around and invokes that same operation on SAP.
The same functions also appear under the TRFC node. TRFC stands for “Transactional Remote Function Call”, though it is not really transactional. When you invoke an operation which appeared under the TRFC node (the Action is different for the same function under the RFC and TRFC nodes), the adapter when invoking the function on SAP, associates an identifier with the call. the SAP server makes a note of this identifier, and maps it to the execution status of that function. If, (suppose), the SAP server went down before completion of the function execution, the client can, at a later point, re-execute that call with the same identifier. SAP will realize that this call hasn’t been made before, and will attempt to re-execute the function. On the other hand, if SAP had successfully completed the call earlier, but if the client went down before it could process the response (or a network failure occurred before it could receive the response), the client can, at a later point, re-execute that call with the same identifier. SAP will know that an earlier call with the same identifier already completed successfully earlier, and will not execute it again, but will just send a response back to the client. The client, when it gets back a response (either on the first or later retries) should then clean up its own state with respect to the identifier, and also instruct SAP to do the same (via a RFC SDK API call, which the adapter exposes as an operation named “RfcConfirmTransID”). Once this cleanup/confirmation happens, this identifier is “forgotten”, and the next time this identifier is seen, it is assumed to be a brand new identifier / operation call. NOTE that in a TRFC call, SAP does not return any output values – hence the difference in the operation signatures when you compare them for the same RFC under the RFC node compared to the TRFC node.
What about the operations under the IDOC node? Sending IDOCs to SAP requires the client (the adapter) to execute special RFCs named IDOC_INBOUND_ASYNCHRONOUS / INBOUND_IDOC_PROCESS (depending on the version) on SAP. Receiving IDOCs from SAP requires the external application (now acting as the server) to be able to handle incoming calls to the same two functions. Operations under the IDOC node in the adapter are hence “dummy” operations which the adapter exposes – the adapter gets metadata for all available IDOCs on the SAP system, and exposes “Send” and “Receive” calls with different complex parameters based on the IDOC you want to work with. At runtime, for outbound calls, the adapter takes the individual pieces of data and converts them to the format which the above mentioned two RFCs require. For inbound calls, it splits/parses the data which it received from SAP as incoming parameter values to the above two RFCs, looks at the data to figure out which IDOC is being transmitted, gets metadata for that IDOC, and then re-formats the data to fit that metadata.
Note – for inbound IDOC calls, the adapter exposes a property named ReceiveIdocFormat (which is an enumeration of type IdocReceiveFormat). One of the values in this enumeration is “Rfc” – if you choose this, then, for incoming IDOCs, the adapter won’t peek at the data to figure out which IDOC it is and re-format it to fir the “Receive” operation – instead, it will just expose it as a RFC call to the servicehost – as a call to IDOC_INBOUND_ASYNCHRONOUS / INBOUND_IDOC_PROCESS. Well, actually, it will expose it as a TRFC call (i.e. the WCF message will have the TRFC action) since when SAP sends IDOCs to an external application, it usually does that using transactional semantics – at least SAP’s mechanism of transactions. (Note - the default value of the property/enumeration is “Typed”).
Lastly, BAPIs. SAP allows you to create Business Objects, and with each object, associate methods, events, attributes, etc – its version of OOP. In the current SAP architecture, methods on business objects are just RFCs. Hence, at design time, the adapter looks up the Business Object Repository (BOR), and determines the objects present on SAP. For each object, it figures out what methods were defined for it, and what the actual implementation is – that is, what is the actual RFC to which it maps. The adapter then shows the friendly names of the operations in the UI (since it has that information when it looked up the BOR), but in the Action , it actually uses the RFC name – since at runtime, what it really needs to do is just execute the corresponding RFC.
And now, coming to the question which this post was meant to answer – why is there no BAPI node in the UI for an inbound contract? When the adapter receives an incoming call from SAP, all it has is the function name – since after all, as you can see above, everything just involves execution of an RFC – for both outbound and inbound calls. If SAP sent along a “transactional” identifier with the function invocation, the adapter formats the incoming message (which it gives to the WCF service / BizTalk Receive Location) as a TRFC call. If the function which SAP invoked was named IDOC_INBOUND_ASYNCHRONOUS / INBOUND_IDOC_PROCESS, the adapter recognizes these functions as “special”, and peeks at the data to determine the IDOC being transmitted, and formats the WCF message as a call to “Receive” with the appropriate action (which contains the IDOC type, among other things). Else, the incoming WCF message is now just formatted as a call to an RFC (with the Action containing the RFC name).
The adapter can’t format the WCF message using an Action corresponding to a specific Business Object (i.e., corresponding to an operation that would live under the BAPI node), since strictly speaking, there is no mapping from the RFC name to the Business Object type which implements it. The BAPI node in the UI for outbound contracts was really more of a convenience mechanism – so that a user can navigate the business object hierarchy to find the function of interest. All functions under the BAPI node also appear under the RFC and TRFC nodes too. For inbound calls, if you’re interested in listening for an incoming function like BAPI_SALESORDER_CREATE (which is most probably a function with the friendly name “Create” defined on the “SalesOrder” business object), just search for the RFC named BAPI_SALESORDER_CREATE under the RFC node (or under the TRFC node if you know that SAP is going to execute this “transactionally”). NOTE that in the most common case, only the “special” IDOC RFCs are invoked transactionally by SAP on an external application, so for all other incoming RFC calls, you should just add the operation under the RFC node to your Service Contract.
If you’ve used the SQL Adapter in CTP3 of the BizTalk Adapter Pack V2, you’ll notice that there are two nodes in the Metadata Hierarchy for Stored Procedures – one is named “Procedures”, and the other is named “Strongly-Typed Procedures”. What’s the difference, and what are the scenarios which they are meant to solve? What are their limitations? I’ll try to explain all this, in this post.
1. The first method of executing Stored Procedures which we added in the adapter, manifested as the operations under the “Procedures” node in the Metadata hierarchy. The operations here have the action “Procedure/<database_schema_name>/<procedure_name>”. For these operations, the adapter reads the System Tables in SQL Server, finds out the parameters to the procedure, and exposes them as IN or INOUT parameters in the WSDL. However, we yet need a way to return the result sets which the Stored Procedure can return at execution time. The adapter exposes these result sets as a return parameter, of type System.Data.DataSet[]. Being an array, multiple result sets can be returned.
At runtime, the adapter executes the Stored Procedure using the ADO.NET function SqlCommand::ExecuteReader(). Each result set returned by executing the Stored Procedure is serialized into its own DataSet; hence all of them together appear as DataSet[]. The response XML message contains both, the schema for each result set / DataSet, as well as the actual data in that result set / DataSet. When used in a .NET application, the schema + data together are used when deserializing the SOAP message into a DataSet object.
Advantages: Works for all stored procedures.
Limitations: The result sets are loosely typed (being a DataSet[]), and in BizTalk, are not helpful if you want to use the Mapper.
Workarounds: What you could do is, execute the procedure once, dump the XML message to a file location, and open it in notepad. Select the <schema> node within the result set section in the return parameter, copy-paste it into a new file, and save it with the .xsd extension. You now have a schema which you can deploy in your BizTalk orchestration/project. Also, use the Message Template feature in the WCF-Custom/WCF-SQL port configuration, using a XPath query to only select out the data (at runtime) for that particular result set (matching the XSD which you deployed); ignoring the other result sets (if any) and out parameters. You now have a XML blob being submitted to BizTalk, which conforms to the XSD which you deployed.
2. The next approach we took, was to try and expose the result sets as “strongly typed”, instead of the loosely typed DataSet[] above. These operations manifest as the operations under the “Strongly-Typed Procedures” node in the metadata hierarchy. The action is of the form “TypedProcedure/<database_schema_name>/<procedure_name>”. For these operations, the adapter reads the System Tables in SQL Server, finds out the parameters to the procedure, and exposes them as IN or INOUT parameters in the WSDL. In order to expose the returned result sets in a “strongly typed” fashion, the adapter needs to know what the metadata for the returned result sets will look like. For this purpose, at design time, the adapter executes the Stored Procedure using the ADO.NET function SqlCommand::ExecuteReader(CommandBehavior::SchemaOnly). This translates to the SET FMTONLY ON option being used. However, in order to execute the Stored Procedure, the adapter needs to pass in values to the parameters. For this purpose, the adapter uses DBNull.Value for each parameter. Upon execution, the adapter gets back multiple (empty) result sets, and from these, the adapter can obtain the metadata for the result sets which can potentially be returned at runtime.
Note – I use the word potentially. This is because, at runtime (FYI – the adapter uses the ADO.NET function SqlCommand::ExecuteReader() at runtime), a Stored Procedure can return different result sets based on the input values. For example, if an input parameter has the value 1, the Procedure could return a result set by performing the operation “SELECT * FROM TABLE1”. If the input parameter has the value 2, it might actually execute “SELECT * FROM TABLE2”. At runtime, only one of the two result sets will be returned, depending on whether the input parameter is 1 or 2. However, at design time, when the SET FMTONLY ON statement is used, both the (empty) result sets are returned. The adapter exposes them both as complex out parameters (and names them in the metadata as TypedProcedureResultSet1, TypedProcedureResultSet2, etc). At runtime, one will be null, while the other will have the appropriate data filled in.
Note – The current design of the WCF LOB Adapter SDK (on which the SQL Adapter is based) is that metadata is also required at runtime. Hence, at runtime too, the adapter executes the Stored Procedure using SET FMTONLY ON (just once) to obtain the metadata. Then, for every message being passed to the adapter, the adapter executes the Stored Procedure, and based on the result sets returned, tries to determine whether it should be serialized as TypedProcedureResultSet1, or TypedProcedureResultSet2, etc (for example) (based on the metadata it obtained earlier by using SET FMTONLY ON).
Advantages: Strong typing of the result sets.
Limitations:
- These operations cannot be used to execute “complicated” Stored Procedures – for example, a hypothetical procedure which returns multiple result sets (from potentially different tables) within a loop. This is because the adapter won’t be able to figure out which result set (at runtime) needs to be serialized as which complex type (TypedProcedureResultSet1, or TypedProcedureResultSet2, or something else).
- These operations won’t work for Stored Procedures, when in the procedure code, a temporary table is created, and then, one of the returned result sets is obtained by doing a SELECT on that temporary table. The reason being, when the SET FMTONLY ON option is used, no temporary tables are created. However, when the SQL execution engine comes across the line SELECT * FROM #temptable, it throws an error, (something to the effect of it not finding an artifact named #temptable), since it never created this artifact in the first place (since SET FMTONLY ON is not supposed to make any changes on the server).
- I mentioned above that during metadata retrieval time, the adapter needs to pass in values for all parameters to the Stored Procedure, and for this purpose it passes in DBNull. Now, if the Procedure internally calls a System Stored Procedure, and passes in one of the input parameters (which in our case is NULL), and if the System Stored Procedure returns an error if it sees the value as NULL, the adapter will get an exception at metadata retrieval time. NOTE – this won’t happen in your custom procedure if your code itself throws an error if it sees an input parameter having the value NULL, since for user procedures, when SET FMTONLY ON is true, the execution engine skips the evaluation of the if statements (this evaluation only happens in system stored procedures).
Workarounds: Use one of the other Stored Procedure execution methods.
3. In CTP4 (releasing end of October 2008), we’ve added one more mechanism to execute Stored Procedures. This was mainly done for backward compatibility.
The earlier SQL adapter required result sets returned from Stored Procedures to use the FOR XML syntax. This was because the old adapter used SQLXML; using FOR XML would instruct the SQL Server to return the result set as a single XML value, and the SQLXML code on the client would then parse the XML returned.
In methods 1 and 2 above, you’ve seen that the new adapter used the ADO.NET SqlCommand::ExecuteReader() function. If this function was used to execute a Stored Procedure which returned a result set using FOR XML, all the adapter would see is a single XML value (exposed as a string) – i.e., the adapter thinks that the returned result set only has one column. Even worse, if the XML value was large, it would get split into multiple rows. This is of course extremely cumbersome to work with.
For this purpose, a third mechanism was added, what I call “XML Procedures”. The action for such operations is “XmlProcedure/<database_schema_name>/<procedure_name>”.
There is no design time experience for such procedures. Generating metadata involves the following:
- Generate metadata for the same procedure under the “Procedures” node – this generates the metadata for the request message as well as the response message. Here, we are only interested in the request message schema, since that’s the format in which the request message needs to be sent (though you need to use the “XmlProcedure” action).
- In SQL Server Management Studio, edit your Stored Procedure, and add the XMLDATA keyword at the end of the FOR XML statement (similar to what you would have done if using the older SQL Adapter).
- Execute your Stored Procedure from SQL Server Management Studio. Before the actual data, you should see a <schema> node which has the metadata for the result set. Copy-paste the schema into a .xsd file. Also, add a root node (with namespace) to encapsulate the nodes in the data/schema (you’ll see why later).
- This .xsd file will serve as the schema for the response message in BizTalk.
- Remember to remove the XMLDATA keyword from the Stored Procedure code after you're done with the above steps.
At runtime, you need to do this:
- Specify values for the XmlStoredProcedureRootNodeName (mandatory) and XmlStoredProcedureRootNodeNamespace (optional) binding properties. The XML obtained by executing the “FOR XML” Stored Procedure will be wrapped within this root node, with this namespace.
- Use the “XmlProcedure/<database_schema_name>/<procedure_name>” action during execution (instead of “Procedure/<database_schema_name>/<procedure_name>”).
When the adapter sees the XmlProcedure action, it uses the ADO.NET SqlCommand::ExecuteXmlReader() function.
Advantages: You can continue using FOR XML Stored Procedures side-by-side with the old SQL Adapter. Can work with all stored procedures which return XML.
Limitations: There is no way to generate the metadata using the adapter, since the WCF LOB Adapter SDK does not have a way to accept input parameter values during design time. The adapter cannot use NULL for the parameter values (unlike how it did in 2 above), since obtaining metadata involves actual execution of the Stored Procedure, and therefore all the Stored Procedure logic comes into play, which might throw an error if an unexpected value is seen for an input parameter.
Workarounds: Use one of the other Stored Procedure execution methods.
In the BizTalk Adapter Pack V2, we are adding support for execution of SAP Queries.
A brief one liner on SAP Queries – you can use the SAP GUI designer to create something similar to a “SQL Query” (similar to how you would do it in Microsoft Access) by graphically selecting the tables you’re interested in, the columns, the input parameters, the sort order of the result set, etc.
You can execute the query from the SAP ADO Provider, using syntax like:
EXECQUERY QueryName @P1=Parameter1Value, @P2=Parameter2Value, @USERGROUP=UserGroup [, @WORKSPACE=’X’]
@Pn refers to the nth selection field specified during the creation of the Query.
@USERGROUP (mandatory) refers to the User Group associated with the Query. If the Query was defined in the global workspace, you would additionally specify @WORKSPACE=’X’ in the command.
You can refer to the documentation on additional information on the syntax.
Having the ADO Provider support SAP Queries means that you can also execute them from SSIS Tasks, as well as create SSRS Reports based on them.
In order to use SAP Queries, you’ll have to obtain the next CTP of the BAP 2.0 (scheduled end of October 2008). Note that this CTP is only available to TAP customers.
Attached to this post is a standalone .NET Windows Form application which can be used to browse artifacts on SAP, and build queries which the SAP ADO.NET provider can understand. The main aim is to reduce the grunt work required when building the queries.
The provider supports EXECuting RFCs, SELECTing from a single table, and SELECTing from two tables (JOIN). The tool I’ve attached currently supports EXECuting RFCs and SELECTing from a single table; support for generating queries which use a JOIN will be added in a later version.
The current version of the tool is linked against the 32 bit version of CTP3 of the BizTalk Adapter Pack V2. I’ll update the attachment as and when newer versions of the Adapter Pack are released.
Attachment(s): Microsoft.Data.SAPClient.DesignUI.zip
CTP3 is here and we have added a lot more features based on some feedback we have been getting from various channels. So here is the list of major enhancements
SQL adapter:
1) Strongly typed polling
2) Strongly typed stored procedures
Oracle Adapter
1) Inline Value support
2) App context initialization
3) Oracle Notifications
4) ReadLOB/Update LOB operations
5) PLSQL tables inside RecordTypes
6) Performance improvement for metadata generation
Others
1) We now support upgrade from APv1 RTM to APv2 CTP3
2) More meaningful schema names for SQL adapter - again a common feedback from customers, we will be doing this for other adapters in next releases
3) Using the SAP ADO provider from SSRS
4) Making WCF LOB adapters more discoverable - one of the common requests was that users have to select WCF-Custom and then sqlbinding or sapbinding from yet another menu in the BTS admin console while configuring ports causing problems with usability and troubleshooting.This has now been fixed and you can choose WCF-SQL or WCF-SAP from the list of send ports.
So go ahead and try out the new CTP and do send in feedback. As always, if you want to try this CTP you need to join the TAP(Technology Adoption Program). You can see the details for joining TAP here .
Thanks
Vivek Krishna
We're exicted to announce the availability of the new BizTalk Adapter Pack Poster.
This poster covers interoperability with Line-of-Business Applications using the BizTalk Adapter Pack and the Windows Communication Foundation (WCF) LOB Adapter SDK.
It depicts the functionality, components, architecture, and usage/hosting scenarios of BizTalk Adapter Pack 2.0 and of the WCF LOB Adapter SDK. When printed in full scale, this poster size is 38”x 26”.
The poster in PDF format is available for download in the Microsoft Download Center.
We have designed this poster to promote the adoption of the Adapter pack and of the Adapter SDK. This would simplify service enablement and interoperability with Line of Business Applications or any metadata-rich systems.
Attachment(s): adapterposter.jpg
We’ve had a few users complaining about the throughput of the adapters (the Adapter Pack ones, and/or custom ones) coming to a standstill during normal operation within BizTalk. In this post, I’ll explain why that happens, and some workarounds.
Let’s say BizTalk has received 100 messages which it needs to send to the adapter on a Send Port. Assume that it queues 50 work items on 50 thread pool threads, with each work item containing 2 messages to be processed.
Now, on each of the above thread pool threads, the logic used is something similar to:
1. for (int i=0; i < numberOfMessages; i++)
2. {
3. IRequestChannel channel = CreateNewIRequestChannel();
4. channel.Open();
5. channel.BeginRequest(message[i], callbackFunction);
6. }
IRequestChannel above maps to an adapter channel – IOutboundHandler in the Adapter SDK.
Now, many of the LOB systems for which the adapters are written are do not have an asynchronous version of the LOB API or SDK, i.e., the LOB API is synchronous – blocks the current thread while the invocation occurs. However, as can be seen above, a call to BeginRequest() is made, which means that the caller wants the adapter channel implementation to return immediately. Therefore, in the Adapter SDK, the implementation is:
7. BeginRequest(Message message, Callback callbackFunction)
8. {
9. ThreadPool.QueueUserWorkItem(channel.Request, message, callbackFunction);
10. return immediately;
11. }
What the Adapter SDK does is, since it needs to return immediately, it queues an additional work item in the thread pool queue. The work item, when run, will call Request(), which is a synchronous call, which maps to IOutboundHandler::Execute() in the adapter, with the message as the parameter. Once the adapter finishes processing the message (synchronously) on the thread pool thread, the Adapter SDK takes care of invoking the callback function to notify BizTalk that the message has completed processing, and to hand the response message to BizTalk.
Also, one more piece of information – the channel.Open() call in line number 4 above, is handled by the Adapter SDK. When the Open() call reaches the Adapter SDK, it tries to obtain a free connection from the connection pool, or create a new connection to the LOB provided the maximum number of connections hasn’t been reached. If it is unsuccessful in both attempts, it blocks until a connection becomes available. Note - The adapters in the Adapter Pack expose a setting (typically named MaxConnectionsPerSystem, or MaxPoolSize, etc); the value of which is passed on to the Adapter SDK via a setting it exposes; custom adapters might expose something similar which the end user can tweak.
Given the two blocks of code above, and the behavior of the Open() call, it is now easy to come up with a situation where you can see a throughput stall.
Assume that the maximum number of threads in the thread pool is 100. Also, let’s say that the maximum number of connections allowed to the LOB system is 10. You receive a large number of messages from your Receive Location (for example, 500?), and BizTalk, in all its enthusiasm, queues up all messages (one message per work item – lets refer to these work items as W1) on threads from the thread pool. Therefore, you have 100 threads (suppose, since its the maximum number of allowable threads in the thread pool), all of them beginning to execute lines 1 to 6 above. Only 10 threads are able to proceed past line number 4, since they were able to open a connection to the LOB (your maximum number of connections is configured to be 10, remember?). The other 90 threads are blocked at line number 4, waiting for a connection to become available.
Of the 10 which passed 4, they proceeded to 5, which means they now enter lines 7 to 11. They all queue up work items (lets call these work items W2) on the the thread pool (courtesy line number 9), and return immediately. These 10 threads are now freed up, which enables them to go and pick more items to work on from the thread pool queue.
Now, the actual processing against the LOB (and the real usage of the connection which was successfully opened in step 4) only happens when W2 gets a chance to do its work. However, the 10 threads which were freed up, they won’t process W2. Why? Because ahead of W2, there are yet 400 more W1 work items (BizTalk had queued 500 items, remember) in the queue. Hence, the 10 threads will pick up 10 more W1 work items. These work items of course can’t progress since they are in the same state as the other 90 – there is no connection available. And a connection won’t become available until a W2 work item will relinquish it, but it can’t since it can only do its work once a thread becomes available, and that will only happen when one of the current 100 threads (which are all stuck on line 4) gets freed up, and …… and you have a thread pool starvation problem.
How do you work around this? A number of ways, and possibly, you need to use a combination of some of them (at least the ones in your control) to work around this:
- One point where the threads are stuck is on line number 4. Easy – just increase the number of connections available to the LOB to a really large number, and your problem goes away. Right? Well, you might be prevented from doing that based on server resources, licenses, etc.
- Lines 7 to 11 are responsible for queuing additional work items in the thread pool queue. You could avoid that if there was no need to queue a work item – for example, if your LOB exposed an asynchronous API, you could directly invoke that, passing it the callback routine pointer to “call back” on once it completed, and you could return immediately after kicking off that asynchronous API. Of course, this is not always possible. For example, LOBs like SAP and Siebel – the SDKs which we use did not offer an asynchronous API which we could use in the adapters.
- One of the limitations which led to this situation, was that the maximum number of threads in the thread pool was limited. If you could increase the number of threads in the pool to a sufficiently large number, you wouldn’t see this problem. BizTalk exposes a setting for increasing the number of threads – see http://msdn.microsoft.com/en-us/library/cc296779.aspx. Note that you should try this out in your environment, since increasing it to a large value could possibly hurt performance.
- In my example above, I’ve assumed that if there are 100 threads in the thread pool (maximum), all threads are being used during the processing of messages for my adapter. This is not always the case – the BizTalk runtime (and possibly other artifacts in the same process) will compete for threads from the thread pool, leaving lesser threads for you to work with. One way around this is to configure the send handler for your adapter to run in its own host instance (which means a separate process), leading to lesser contention for the threads from the pool in your process.
- If you look at the code again, you notice that the main reason you got into this situation, was BizTalk itself – it queued up a huge number of messages (lines 1 to 6). The link mentioned above contains the information on settings which control the number of messages BizTalk processes simultaneously; tweaking something here can possibly help.
The safest way would be to ensure that the number of messages BizTalk hands to the adapter (lines 1 to 6) is the same as the maximum number of connections allowed to the LOB. I’m not 100% sure, but I think the In-Process Messages Per CPU setting can be used for this purpose; I’ll have to go through all the documentation for the throttling settings a little more thoroughly – meanwhile, you can do the same (the link available in point number 3 above seems to be the place to start).
One of the new features which we’re supporting in the BizTalk Adapter Pack V2 is the ability to use the SAP ADO.NET Provider from SSRS. In this post, I’ll briefly outline the steps you need to perform in order to get this to work.
Firstly, install CTP3 of the WCF LOB Adapter SDK V1 SP2, and the BizTalk Adapter Pack V2 (making sure that you install the SAP ADO Provider).
Next, you need to make a few changes to the SSRS related config files, in order to have the provider show up in SSRS projects:
- Modify the RSReportDesigner.config file under the Visual Studio 2005 installation directory (on my machine, this is present at C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies).
Add the entry below
<Extension Name="SAP-AP"
Type="Microsoft.Data.SAPClient.ReportingServicesExtension.SAPConnectionWrapper,
Microsoft.Data.SAPClient.ReportingServicesExtension,
Version=3.5.0.0,
Culture=neutral,
PublicKeyToken=31bf3856ad364e35"/>
to the Data section. Add the entry below
<Extension Name="SAP-AP" Type="Microsoft.ReportingServices.QueryDesigners.GenericQueryDesigner,
Microsoft.ReportingServices.QueryDesigners"/>
to the Designer section.
- Modify the RSReportServer.config file under the SQL 2005 installation directory (on my machine, this is present at C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer).
Add the entry below
<Extension Name="SAP-AP"
Type="Microsoft.Data.SAPClient.ReportingServicesExtension.SAPConnectionWrapper,
Microsoft.Data.SAPClient.ReportingServicesExtension,
Version=3.5.0.0,
Culture=neutral,
PublicKeyToken=31bf3856ad364e35"/>
to the Data section.
NOTE: I’ve attached the .config files, as they appear on my machine, so that you can have a look at what the .config entries should look like after modification.
We’ll now create a simple SSRS project using the “SAP-AP” provider.
- Start Visual Studio 2005, create a New Project using the template under Business Intelligence Projects –> Report Server Project. Click OK in the dialog below.
- Right click on the “Shared Data Sources” node, choose “Add New Data Source”.
- Create a new data source named “SAPDataSource”, as shown in the screenshot below:
- In the above dialog, in the Credentials tab, make sure that “No Credentials” is selected (since we’re entering the credentials directly in the Connection String). Click OK to save the information and close the dialog.
- Right click on the “Reports” node, and choose “Add New Report”. Choose the “SAPDataSource” entry from the “Shared Data Source” drop down list. Click Next to move to the next dialog.
- Enter the query to use in the Query String section. Click Next a few more times (the dialogs after this allow you to customize the layout – for now, you can stick with the defaults).
- Right click on the project node in Solution Explorer, to set the start-up report. In the screenshot below, I’ve set the “StartItem” to the newly created report above (named Report1.rdl). (Note – I had named my project “Report Project1DEL”). Click OK to close the dialog.
- Hit F5 to run the Project. A screen similar to the one below should appear. Note that there is an empty text box labeled “P1” below – this is where you would enter the value to use for the P1 parameter, in our query which we used earlier (“SELECT TOP 10 KUNNR, NAME1 FROM KNA1 WHERE NAME1 LIKE @P1”). I’ve entered the string “AB%” – which thus means that I want the first 10 customer numbers and names from the KNA1 table, where the customer name starts with “AB”.
- Click the “View Report” button (top-right corner) to execute the query and view the data. On my machine (running against my SAP server), I see the data below:
Question – do you have a need to use the Siebel ADO.NET Provider from SSRS? If so, please leave a comment here (leave your email address too, so that we can contact you in case we need to understand your scenario more, etc).
Attachment(s): SSRSConfigFiles.zip
One of the features we’ve enabled in the CTP3 of the BizTalk Adapter Pack V2 (which requires CTP3 of the WCF LOB Adapter SDK SP2), is the ability to expose the Adapters (which are in reality, WCF Bindings) as native BizTalk Adapters. Prior to this, if you wanted to use the Bindings from BizTalk, you had to use the WCF-Custom Adapter in BizTalk, then select the WCF Binding, and follow that route.
With CTP3, there is now an additional way you can use the adapters.
- Firstly, install the CTP3 of the WCF LOB Adapter SDK SP2, and the CTP3 of the BizTalk Adapter Pack V2.
- Open the BizTalk Server 2006 Administration Console
- Navigate to the node as displayed in the screen shot below, right click in the empty space, and choose New->Adapter. A new dialog opens up.
- Assuming you have the SQL and/or SAP Bindings installed during the installation of the Adapter Pack, you should see one or two entries in the “Adapter” drop down list, “WCF-SQL” and “WCF-SAP”. For the purpose of this blog illustration, choose WCF-SAP. Enter “WCF-SAP” in the Name text box, and press OK twice to close the dialog.
- In the administration console, navigate to an existing application, and create a new Static Solicit-Response Send Port. You should now see WCF-SAP appear in the drop down list containing available Transport Types.
- Click Configure to configure the transport. A new dialog opens up, containing a number of property pages.
- The “General” tab looks similar to what you would see had you chosen WCF-Custom as the transport type. However, there is one addition. There is a “Configure” button above the text box where you enter the URI. You can click it to bring up a Connection Uri Builder Dialog.
- If you navigate to the binding tab, you see that you no longer need to choose a binding. Also, the binding properties are displayed in a more user-friendly manner, with descriptions of the properties appearing at the bottom of the property grid.
- The other four tabs (Behavior, Credentials, Messages, Import/Export) are the same as those which appear in the WCF-Custom adapter.
In CTP3, the above is available only for the SQL and SAP adapters. For the next CTP, we plan to do this for the OracleDB and the OracleEBS adapters also. Question – do you want this for the Siebel adapter too? If so, leave a comment here.
Also, in CTP3, this is being done only for BizTalk 2006 R2. For the next CTP, we plan to have this for BizTalk 2006 R3 also.
Firstly, if you haven’t already seen it, have a look at the post describing the EnableSafeTyping binding property - http://blogs.msdn.com/adapters/archive/2007/11/24/the-enablesafetyping-binding-property.aspx.
Since the Adapter Pack V1 released, we have had requests from users, wherein, they want to expose the Date (DATS) and Time (TIMS) data types as a .NET DateTime (strong typing), but yet want the adapter to be able to handle common SAP values such as 00000000 for DATS (which in SAP would mean a MINVALUE or NULL for the Date field). Besides, we already know that SAP doesn’t validate the data in DATS, TIMS and NUMC fields, so you might end up receiving “invalid” data from a SAP RFC, but you yet want the strong typing in your client code.
As a result, we’ve added a new binding property named “DataTypesBehavior” in the SAP adapter. This property is actually a “complex” property, containing 12 sub-properties. I’ll first list the names of the sub-properties.
- DateTimeMaxToDats
- DateTimeMinToDats
- DateTimeMaxToTims
- DateTimeMinToTims
- DatsMaxToDateTime
- DatsMinToDateTime
- TimsMaxToDateTime
- DateTimeNullToDats
- DateTimeNullToTims
- InvalidDatsToDateTime
- InvalidNumcToInt
- InvalidTimsToDateTime
- EmptyDatsToDateTime
I’ll explain just a few of the above; you should then be able to figure out what the rest mean.
- DateTimeMaxToDats – this property controls the behavior when converting DateTimeMax (the .NET DateTime.MaxValue value) “To” Dats (the SAP DATS value). The .NET DateTime.MaxValue can only be present in the request message. The adapter needs to convert this to a DATS value when sending it to SAP. Therefore, this property controls the behavior of how the adapter parses the request message.
- DatsMaxToDateTime – this property controls the behavior when converting DatsMax (the SAP DATS MaxValue, which is 99999999) “To” DateTime. DATS values can only be sent from SAP right? And, only a client consuming the adapter can understand .NET DateTime, right? Hence, this property controls the behavior of the adapter, when it receives the DATS value from SAP (as the response to a RFC invocation), and when the adapter is converting this value to XML in order to give the response to the client.
Additional information for each property, including details on the values which need to be specified for each property, can be obtained by peeking through the attached .txt file – it contains the comments present in the source code of the adapter for these individual properties.
NOTE – this binding property is present in the SAP Adapter in the Adapter Pack v2. For the Adapter Pack v1, there is a hotfix available (KB # 954539) – your Microsoft support contact should be able to obtain it for you.
Attachment(s): DataTypesBehavior.TXT
If you’re developing your own adapters against the WCF LOB Adapter SDK, then it might be worth your while to try out the June CTP of the SP2 of the WCF LOB ASDK. There are a few minor changes, but we might make more based on your feedback.
I’m mentioning the changes from SP1 to the June CTP of SP2 in brief. For more details, you should refer to the documentation.
- Support for VS 2008 and .NET 3.5.
- Pre SP2, the ASDK allowed all input channel shapes – IInputChannel, IInputSessionChannel, IReplyChannel, and IReplySessionChannel. In SP2, there is a setting exposed to the adapter, which allows the adapter to control which of the above four shapes should be allowed. This is important when the adapter is used with BizTalk, and you want to integrate the LOB transaction (assuming it understands System.Transaction) with the BizTalk MessageBox transaction – in such a situation, you only want to allow one-way channels to be used. In other cases (SAP for example), where a reply always needs to be sent back to the LOB, you might want to only allow two-way channels.
- Pre SP2, the WSDL generated (based on the operations selected in the Metadata Search Browse (MSB) UI) was always compiled before being returned to the caller (or being converted to a WCF proxy). In some situations (e.g., where the adapter is overriding the XSD generation to return metadata for – let’s say a System.Data.DataSet) – the XSD/WSDL might not compile (even though svcutil.exe can successfully convert it to a WCF proxy). In order to allow this, a setting is exposed to the adapter which determines whether the WSDL should be compiled or not, before being converted to a WCF proxy. The advantage of compilation is that it can catch “real” errors in the wsdl.
- There are two new QualifiedTypes added for System.Data.DataSet. (For “why two” and “why not just one”, refer to the documentation).
- Pre SP2, the Metadata Search Browse (MSB) UI tool by default always showed all authentication options - “Username”, “Windows” and “Certificate”. The adapter can now indicate which ones are supported (and correspondingly, only those appear in the UI). NOTE - This only affects the MSB UI, and does not play a role during runtime.
- Pre SP2, when using the “Consume Adapter Service” tool in a BizTalk project, the XSD files generated had names like “MyBinding1.xsd”, “MyBinding2.xsd”, etc. Even though a “filename prefix” textbox was present, it had a couple of drawbacks - (a) the user had to provide the prefix, which might not clearly indicate what the XSDs contained, and (b) the same prefix applied to all XSDs generated during the metadata resolution of all operations selected in the UI. In SP2, it is now possible for the adapter to specify a “filename hint” for each XSD generated.
The BizTalk Adapter Pack team is very happy to announce the release of the second CTP of the Adapter Pack V2. This CTP adds more features to the new WCF Oracle E-Business Suite and SQL adapters that we shipped in the first CTP. We got some good feedback on the first CTP,and we are doing a second CTP with some more features.We added support for polling a stored procedure and PL/SQL tables in Oracle ebiz adapter and support for SQL query notification in the SQL adapter. Keep the feedback coming!
Note that this CTP is only available for members who have enrolled into the Adapter Pack V2 TAP program. Please view this blog for details on how to enroll into this program.
Enjoy!
The BizTalk Adapter Pack Team
In this post, I’m going to demonstrate how the Polling feature in the WCF SQL Adapter can be used to read messages from SQL Service Broker (SSB) queues and publish them in the BizTalk MessageBox. And to make it really simple, we’re going to do it without an orchestration.
(Note – most of the SSB portion in this post has been based on the SSB tutorial available at http://msdn2.microsoft.com/en-us/library/bb839489.aspx).
The scenario:
- An external source publishes messages into a SSB queue.
- At fixed intervals (the polling interval), the adapter checks the SSB queue to see if a message is available.
- If a message is available, the adapter performs the actual RECEIVE operation to remove the message from the SSB queue and publish it in BizTalk – these operations are performed within the same DTC transaction.
- A filter is used to send the data to a folder on the file system (using the File Adapter)
Creating the database artifacts required for the SSB conversation:
The artifacts which we need to create are:
- A message type, which denotes the format of the messages in the queue
- A contract, which denotes the conversation between a sender and a receiver (the contract also specifies the type of the message which will flow between them)
- The Initiator and Target queues, in which the messages are stored
- The Initiator and Target services, utilizing the above-mentioned queues.
I’m not going to explain the SQL statements below in detail, mainly because this post is not meant to be a tutorial on SSB – you can refer to SQL Server Books Online for more information on SSB.
USE master;
GO
ALTER DATABASE <your db name here>
SET ENABLE_BROKER;
GO
USE <your db name here>;
GO
CREATE MESSAGE TYPE
[//SqlAdapterSSBSample/RequestMessage]
VALIDATION = WELL_FORMED_XML;
CREATE CONTRACT [//SqlAdapterSSBSample/SampleContract]
([//SqlAdapterSSBSample/RequestMessage]
SENT BY INITIATOR
);
CREATE QUEUE InitiatorQueue1DB;
CREATE SERVICE
[//SqlAdapterSSBSample/InitiatorService]
ON QUEUE InitiatorQueue1DB;
CREATE QUEUE TargetQueue1DB;
CREATE SERVICE
[//SqlAdapterSSBSample/TargetService]
ON QUEUE TargetQueue1DB
([//SqlAdapterSSBSample/SampleContract]);
At this point, we’ve created the main database objects required for the SSB side of things.
Creating the BizTalk Artifacts
- Start the BizTalk Server 2006 Administration Console.
- Create a new BizTalk application – let’s call it “SSBPollingApplication”.
- Create a new 1-way Receive Port – call it “SqlReceivePort”. Create a new Receive Location – call it “SqlReceiveLocation”.
- Choose the Transport Type (in the Receive Location Configuration dialog) as “WCF-Custom”. Choose “PassThruReceive” for the Receive Pipeline.
- Click configure, the WCF-Custom adapter configuration dialog pops up.
- In the General tab, enter the URI denoting your SQL Server. The format is “mssql://servername/instancename/databasename”. For example, on my machine (since I am using the default instance of SQL Server, the uri I entered is “mssql://localhost//SSBTestDb” (my database is named “SSBTestDb”)).
- In the Binding Tab, choose the binding as “sqlBinding”. In the configuration properties which are displayed below, set these:
- pollingIntervalInSeconds = 2 (or whatever you're comfortable with)
- polledDataAvailableStatement = SELECT COUNT(*) FROM TargetQueue1DB WITH (NOLOCK)
- pollingStatement = (note, multi line statement follows):
DECLARE @DlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvMsg XML;
RECEIVE TOP (1)
@DlgHandle=conversation_handle,
@RecvMsg = CAST(message_body as XML)
FROM TargetQueue1DB;
IF NOT (@DlgHandle IS NULL)
BEGIN
END CONVERSATION @DlgHandle;
SELECT @RecvMsg AS ReceivedMessage;
END
- A brief explanation of the 2 SQL blocks above:
- In the polledDataAvailableStatement, we’re checking if there are any rows in the SSB queue. It is this statement which is going to execute every “pollingIntervalInSeconds” seconds. If a non-zero value is returned, the adapter interprets it to mean that data is available, and only then proceeds to execute the pollingStatement.
- In the pollingStatement, we are selecting (and removing – the RECEIVE syntax removes the message, while SELECT would just peek at it) the first message in the SSB queue. We’re also selecting the conversation handle for that message. Also, if we did indeed successfully pick up the message from the queue (maybe someone got to it first?), we end that specific conversation.
- In the Behavior tab, right click on the ServiceBehavior, and choose “Add Extension”. Add the “sqlAdapterInboundTransactionBehavior” behavior. You can control the transaction isolation level (the default is ReadCommitted). It is this transaction isolation level which will be applied to the DTC transaction spanning the BizTalk Message Box and your SQL Server (from where you’re pulling messages from the SSB queue).
- In the Other tab, choose None for credentials (if you want to use Integrated Security) – or specify a username + password.
- Click OK as many times as required to close the Receive Port configuration dialogs.
- Create a new static 1-way send port named “FileSendPort”. Configure the transport type as FILE, and configure the port to drop messages to a valid folder on your file system. Select PassThruTransmit as the Send pipeline. Also, click on “Filters” in the left pane. Add a filter condition:
- Property = BTS.ReceivePortName
- Value = SqlReceivePort
- Click OK to complete the configuration of the Send Port.
At this point, the configuration of the BizTalk application is complete. Start the application.
We’re now going to send messages to the SSB queue (I’m using SQL Server Management Studio – you could also use osql.exe). I used the following SQL block to send a message to the queue:
DECLARE @RequestMsg XML;
SELECT @RequestMsg = N'<TestMessage>Hello, World</TestMessage>';
DECLARE @DlgHandle UNIQUEIDENTIFIER;
BEGIN DIALOG @DlgHandle
FROM SERVICE
[//SqlAdapterSSBSample/InitiatorService]
TO SERVICE
N'//SqlAdapterSSBSample/TargetService'
ON CONTRACT
[//SqlAdapterSSBSample/SampleContract]
WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @DlgHandle
MESSAGE TYPE
[//SqlAdapterSSBSample/RequestMessage]
(@RequestMsg);
Once you send the above message to the SSB queue, within a short while, you should see it in the folder specified in the FileSendPort. The message I see is:
<Polling xmlns="http://schemas.microsoft.com/Sql/2008/05/Polling/">
<PolledData>
<DataSet xmlns="http://schemas.datacontract.org/2004/07/System.Data">
<xs:schema id="NewDataSet" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element msdata:IsDataSet="true" name="NewDataSet">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="NewTable">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="ReceivedMessage" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
<diffgr:diffgram xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<NewDataSet xmlns="">
<NewTable>
<ReceivedMessage><![CDATA[<TestMessage>Hello, World</TestMessage>]]></ReceivedMessage>
</NewTable>
</NewDataSet>
</diffgr:diffgram>
</DataSet>
</PolledData>
</Polling>
As can be seen above, the adapter has returned the message in System.Data.DataSet format. You could ofcourse use an XPath query to extract only the body of the message. Here’s how -
- Navigate back to the SqlReceivePort configuration. Bring up the WCF-Custom configuration dialog (the dialog which contained the General tab, the Binding tab, the Behavior tab, etc).
- Navigate to the Messages Tab.
- In the “Inbound BizTalk Message Body” section
- Select “Path”. Enter this XPath query: /*[local-name()='Polling']/*[local-name()='PolledData']/*[local-name()='DataSet']/*[local-name()='diffgram']/*[local-name()='NewDataSet']/*[local-name()='NewTable']/*[local-name()='ReceivedMessage']
- For the Node Encoding, select “String”.
The data in the file should now just be:
<TestMessage>Hello, World2</TestMessage>
