MS Innovations Blog

On my current project the client has been dabbling with BizTalk 2006 R2 in a very unsupported configuration with Windows Server 2008, and SQL 2008. In previous posts I talked about Windows Server 2008 and some challenges installing BizTalk on this OS. Generally, Windows Server 2008 behaves well for BizTalk. SQL 2008 has behaved well and has not presented any problems for BizTalk other than the fact that it does not include Notification Services so you must install this optional component from the installer included with SQL 2005. This post describes some new difficulties I have identified when using BizTalk Server 2006 R2 with IIS 7.

 

Working in the unsupported environment has been very interesting because it has identified that there are issues with BizTalk's WCF integration on Windows Server 2008. Windows Server 2008 includes IIS 7 which has been overhauled extensively. Several issues have been identified with IIS 7 and BizTalk Server 2006 R2:

 

- It is possible to run the BizTalk WCF Publishing Wizard through completely to create a .svc WCF endpoint and a virtual directory to host the WCF service but by default IIS 7 is not configured to support the .svc extension. In order to setup the .svc extension, use the IIS 7 directions at http://msdn.microsoft.com/en-us/library/ms752252.aspx.

 

- When running the BizTalk WCF Publishing Wizard sometimes IIS 7 will get dumped out completely. From time to time running the wizard the web.config at c:\Inetpub\wwwroot and all of the virtual directories in this folder get removed during the time that the WCF Publishing wizard is running. For this reason, I strongly recommend you backup the web.config in the web root and all virtual directories stored under the web root before running the WCF Publishing wizard on a box that includes IIS 7. If you just registered the .svc handler and IIS 7 gets dumped out you will need to create it again. Due to this issue, I also strongly recommend that if you need to work with HTTP or IIS related adapters, use Windows Server 2003 R2 with IIS 6 rather than Windows Server 2008 with IIS 7.

 

- The BizTalk WCF Publishing Wizard may require additional permissions when run on Windows Server 2008 than on Windows Server 2003 due to the secure by default nature of IIS 7. I identified issues with the NetMSMQ binding and MEX-only endpoints created with the BizTalk WCF Publishing Wizard in which permissions must be loosened while the wizard is running although they may be tightened afterwards.

 

Thanks,

 

 

On my current project I was working to configure the WSS adapter for BizTalk on a separate server than BizTalk. This requires running the BizTalk installer on the SharePoint box and then running the BizTalk Configuration Wizard to setup the WSS adapter web service. I was running the configuration wizard and was just using the defaults and received the following error:

 

d:\depot2300\mercury\private\kw\source\bizoffice\code\bizofficeconfiguration\wssadacfg\wssadacfg.cpp(467): FAILED hr = 80070002

This is a similar error to the one reported at https://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3350889&SiteID=17 but I identified a different workaround that did not require an updated DLL from Microsoft. Rather than use "Default Web Site" as the application where the WSS adapter web service should be installed, I used an already existing web site such as "SharePoint - 80". This enabled me to install the WSS adapter successfully.

 

While discussing this issue I wanted to mention that the WSS adapter configuration instructions at http://msdn.microsoft.com/en-us/library/aa547280.aspx mentioned that you needed to install "Windows SharePoint Services SP2" but this refers to WSS 2 SP2 and is not required if you currently have WSS 3.

 

Thanks,

On a recent project I was exposing a BizTalk orchestration as WCF for a Windows Forms client and a Windows Mobile client. For simplicity, a single BizTalk receive port was being used with multiple WCF receive locations. The BizTalk WCF publishing wizard allows this type of functionality through running of this wizard more than one time. This is by far one of the coolest WCF capabilities in BizTalk - the ability to expose multiple bindings (like you can do in WCF) for each BizTalk orchestration. For example, one BizTalk orchestration can be exposed for both a WCF-BasicHttp binding and a WCF-NetMSMQ binding through running the WCF publishing wizard twice, once choosing to publish a service endpoint for the WCF-BasicHttp binding, and then a second time as a Meta-data endpoint (MEX) for the WCF-NetMSMQ binding.

 

The message body properties for both of these receive locations was set to "Body" which is supposed to pull out the contents of the SOAP:Body from the WCF service call. When testing both of these bindings the contents of the SOAP:Body were being output but they were being wrapped in a part element like in the message given below:

 

The application that was being implemented was one in which the body data needed to be passed along intact and not further wrapped. The MSDN documentation on the Message Body properties (http://msdn.microsoft.com/en-us/library/bb226478.aspx) did not mention that additional data would be passed along to wrap the content that was passed to the WCF receive location. The additional part element caused problems with the next system to receive the message. So I used the Path option rather than Body for the message body and used the XPath expression of /*[local-name()='part'] to remove the additional wrapping of the data. This provided what I would expect to be included in the body part of the SOAP:Body - the data passed to the WCF operation and nothing more.

 

This tip should help you avoid one challenge of working with the new WCF features of BizTalk. Thanks!

I was working on tracking down an issue with MDNs not being received for some AS2 communications. So I decided to enable the AS2 status reporting capabilities in BizTalk. I needed to configure BizTalk so that the BAM Tools functionality was available in order for the AS2 status reports to be available. During the process of using the BizTalk configuration wizard I received an error, which is given below:

 

"BizTalk 2006 Configuration: Microsoft SQL Server 2005 Data Transformation Services (DTS) for BAM Archiving is not installed on the local machine.  Please install Microsoft SQL Server 2005 Integration Services.  (Microsoft.BizTalk.Bam.CfgExtHelper.ToolsHelper" ... Additional information: “Could not load file or assembly ‘Microsoft.SqlServer.ManagedDTS, Version=9.0.242.0”…

 

This error occurred when trying to configure the BAM Tools and I was specifying the database server names and the names of the databases. A red icon with an "X" occurred to the left of the database name. If you did not know, you can double click on the red icon on all of the pages of the BizTalk configuration wizard to see the error. 

 

After looking on the internet I came across two blog posts (http://blogs.msdn.com/mattlind/archive/2006/08/30/730575.aspx, http://geekswithblogs.net/andym/archive/2006/11/27/99246.aspx) that gave me a few hints on what needed to be done but I still felt there were gaps in documentation and the blog posts so I wanted to give a little more information here for other people so that they would not need to come up with their own ideas of what needed to be done. I will also be referring to the multi-server BizTalk installation guide which can be found among the guides here: (http://www.microsoft.com/downloads/details.aspx?FamilyID=df2e8a88-fb23-49a4-9ac7-d17f72517d12&DisplayLang=en). The AS2 and EDI Status Reporting features of BizTalk Server 2006 R2 do provide a huge help in doing diagnostics of EDI interchanges and AS2 communications, but the process of enabling this feature is complicated.

 

A multi-server install usually means that BizTalk is on one (or more) servers and the SQL Server engine components are on some other (one or more) servers. In the BizTalk multi-server install guide, if you are trying to setup BAM Tools (one of the prerequisites for the AS2 Status reporting), it recommends that you install SQL Server Integration Services (SSIS) on the BizTalk box. This is wrong and could represent a license violation if you actually did this. This is one solution but there is actually a different solution which is better. BTW, the BAM install guide does not mention anything about the AS2 Status reporting so you can just skip this document if you are trying to setup the AS2 reporting functionality.

 

Although the error message indicates that the problem is a DLL named Microsoft.SqlServer.ManagedDTS is missing, this does not mean that something about the DTS functionality outside of SSIS is not functioning properly. This refers to a DLL that is installed on the SQL Server box relating to SSIS but not part of it that also needs to be on the BizTalk server. If you open up the SQL Server system and look in the GAC (at c:\Windows\assembly), you will see that this DLL exists on a SQL Server where the "Workstation Tools" have been installed. So all that is required is that you install the SQL Server client tools on the BizTalk server which in the SQL installer are designated as "Workstation Tools". BTW, there is not a way to export a copy of the DLL from the GAC and I would advise against trying to individually copy this DLL out of a CAB from the SQL install because there are also additional DLLs that may need to be on the BizTalk server as well. It is important to note that it is not enough to just have the Workstation Tools installed only on the SQL Server itself. As one of the previous posters mentioned, you should select all of the Workstation Tools components on the install on the BizTalk server. This may require that you insert the install media into the BizTalk server or that you copy the installation software over to the BizTalk server.

 

In Andy Morrison's post, he mentioned he had received an error when installing the SQL client tools (Workstation Tools), regarding OWC11. This is a variation on the install of the client tools. You could get other errors too - the key is to ensure the client tools run through successfully. Right before the client tools are installed you will see a window that goes through a series of prequisite checks for the SQL client tools install and you may need to ensure all of these tests pass with a warning or are successful before the client tools install will work. When I ran the client tools on the BizTalk server I did not receive an error so you may not need to reinstall OWC11 and you may not need to do a repair on the BizTalk installation. Ok, there are just a few more steps.

 

Then after you have run the Workstation Tools on the BizTalk server you will need to close the BizTalk configuration wizard and re-open it in order for the BAM Tools page to successfully see the Workstation Tools and connect to SQL Server appropriately. You can then specify the BAM Tools options and then recheck the AS2 status reporting option under the EDI/AS2 Runtime page. After running through the BizTalk configuration wizard successfully you will then need to open up the BizTalk administration console and go to the Party properties. On your AS2 party you will need to open up the AS2 properties and then on the General tab check to enable the AS2 status reporting. You may also want to check the other boxes on the Sender and Receiver pages of AS2 properties for whether to store the wire and decoded messages of the AS2 communications so that you can drill into this data from the Group Hub page.

 

After all of this configuration, you should restart your BizTalk applications and hosts and then wait a while so that the AS2 status reporting can collect some data. Then go to the Group Hub page and click refresh. The Group Hub page will now have some additional links to reports but you will probably need to scroll down on the group hub page in order to see them. Normally all of the group hub page fits within the standard size window but the additional AS2 status reporting enables the use of the scroll bar so be aware that these links may be off-screen initially when you view the Group Hub page.

 

Thanks,

 

As I am wrapping up my BizTalk EDI project I also wanted to mention that this project has incorporated a nontraditional integration option between BizTalk and PeopleSoft. Microsoft provides the BizTalk Enterprise Adapter Pack which includes the PeopleSoft adapter. This provides a way to integrate between BizTalk and PeopleSoft and provides a handy way for extracting the message schemas of the different services exposed by PeopleSoft via the Compontent Interfaces. These days PeopleSoft itself has its own EDI integration platform as well as the ability to interact with the Filesystem and so there are various layers of infrastructure within PeopleSoft that you can choose to integrate over.

 

On my project it was possible to map PeopleSoft flat-file formats to EDI message types and exchange the files over the network and then out to the VAN. So rather than work directly with PeopleSoft, the solution was to work across a file system, receiving PeopleSoft flat files and transforming them to EDI and then on receipt of EDI messages, transforming them into PeopleSoft flat file acknowledgements. In my opinion this was a less difficult implementation option than the use of the PeopleSoft adapter because you did not need to configure a Java environment or install a PeopleSoft component interface. Next I will give some details of the PeopleSoft flat-file format to give you a little more information about how involved the mapping was in this solution.

 

The PeopleSoft flat file format was based on 3 groups of positional lines, the first group always has one line for each transaction (multiple in a batch), then the second group has n lines below the first group and the third group has m lines below the second group. The first group is tagged with 000A, the second group with 100A and the third with 200A. So for basic EDI messages this nesting of groups corresponded pretty well with the traditional PO1, N1, and SCH loops. Unfortunately the message format in PeopleSoft is configurable in the PeopleSoft administrator website and it is possible to add or remove columns very easily so it is possible that a message format could be changed and result in a disruption of the BizTalk application that uses the messages. 

 

The benefit of using the PeopleSoft adapter is that you have a sure reference for the generation of the message types. This was by far the largest challenge of the project; knowing the format details for the PeopleSoft adapter is not documented that well and is variable based on the message type configuration. But if you do not have the security option to install a custom component interface for the PeopleSoft adapter or have other difficulties getting the PeopleSoft adapter to work, this post has provided you with yet another option for integration.

 

Thanks, 

I have been in the final stages of a large BizTalk EDI project and I have been struggling with the appropriate infrastructure for managing a single AS2 HTTP endpoint that interacts with a VAN over which multiple trading partners' data is sent. The main challenge has been that if a single endpoint to BTSHTTPReceive.dll is exposed, it will provide data for many trading partners yet these partners may be used across one or more BizTalk applications. Since the endpoint usually exists as a 2-way receive port with As2EdiReceive/As2Send as the pipelines, I have been using send ports with filters to catch the messages after they are pushed to the MsgBox from the exposed endpoint. When just a single BizTalk application exists that includes the endpoint, all messages can be routed easily. But when more than one BizTalk application exists, it becomes much more complicated.

 

For example, if you have the exposed endpoint in Application 1 and two send ports in Application 2, these send ports will be able to pick up messages received on the exposed endpoint but you will see errors being logged from Application 1 that the messages could not be routed successfully. For this reason, I have found that it is important to have send ports that are used for picking up messages received through BTSHTTPReceive.dll in the same BizTalk application as the receive port for the endpoint. So I have written to disk the messages received from the endpoint that are used in other BizTalk applications as a way of offloading the messages to processes and orchestrations in the other BizTalk applications.

 

One problem with this approach is that sometimes the messages received in Application 2 do not have the correct ISA/GS headers or may have been altered through the application of the Global EDI properties. These properties can make understanding BizTalk routing very painful because they frequently cloud or disguise the intended recipient of messages. In my project, messages that are received in Application 2 have been placed in the correct folder so I know that the routing infrastructure was able to match the ISA and GS headers to the party properties, yet at some point during the routing the Global EDI properties were applied anyway. Today I identified a workaround for a situation in which a message has been routed with incorrect headers.

 

For send ports that have filters that write messages out to offload to other BizTalk applications, you can use a PassThruTransmit pipeline on the send port filter to write the body of an EDI message out to disk. You are probably thinking that this is crazy because this will represent the XML of the EDI message body. For me this worked great because the EDI message body stayed intact even when the EDI global properties got applied. Then on a receive port you can use the XmlReceive pipeline to pull in the message text written out via PassThruTransmit, which is an XML version of the EDI message body. This identifies the root of the message and identifies that it is the XML version of the message. In essence this functions a lot like an EDI receive without needing to worry about the resolution of the ISA/GS headers to determine the namespace because the namespace is included on the root of the EDI-XML message. So to summarize the process of getting an EDI message body from an exposed AS2 endpoint when the EDI global properties get messed up, use the following pipeline pattern in your ports:

 

BTSHTTPReceive.dll --> (R) AS2EdiReceive / AS2Send --> (S with Filter) PassThruTransmit --> (R) XmlReceive

 

Good luck to everybody working with EDI in BizTalk. Its easy once you get the hang of it, but until then its usually bumps and bruises. :)

 

Thanks,

On my most recent project I am employing send port role links as a way to route messages within BizTalk for test and production EDI trading partners. This is one way to use a single orchestration for handling multiple trading partners (BizTalk parties). So in my orchestration instead of simply having a send port, I have a role link that contains a send port. Since the project's deployment simply consists of a single server I am deploying directly from Visual Studio. During a recent round of changes I encountered an error that was consistently blocking deployment from Visual Studio. This error is given below:

 

Failed to update binding information.
Could not validate TransportTypeData, Address or Public Address properties for Receive Location <Receive Location Name>. Retrieving the COM class factory for component with CLSID {CFF09884-6582-49C2-A74F-0FD85849281E} failed due to the following error: 8007045a.

 

Another variation of this error is:

 

Failed to add resource(s). Change requests failed for some resources. BizTalkAssemblyResourceManager failed to complete end type change request. Failed to update binding information. Could not validate TransportTypeData, Address or Public Address properties for Receive Location <Receive Location Name>. Retrieving the COM class factory for component with CLSID {CFF09884-6582-49C2-A74F-0FD85849281E} failed due to the following error: 8007045a.
 
These errors occur when trying to redeploy over BizTalk applications that use role links with send or receive ports. These deployments from Visual Studio have been attempted with the deployed BizTalk applications in both the enlisted/started and unenlisted/stopped states. I have found that these errors occur for me on more than one BizTalk development system, on both Windows XP and Windows Server 2003 systems, both running BizTalk Server 2006 R2. I was able to determine a modified deployment process to enable deployment to be successful in these scenarios. Here are the steps I came up with in order to successfully deploy a project with role links which is my workaround for the errors above:

 

1. Build/rebuild in Visual Studio in the build configuration you want to deploy under.

2. Stop the BizTalk application you want to deploy to.

3. Export the latest bindings for the BizTalk application. This will include information about all of the send and receive ports and role links. You may want to check to include the global party information in a separate file for an exhaustive binding backup.

4. Unconfigure the receive ports by right-clicking on the BizTalk application and clicking "Configure...". I would specify <None> for the receive port bindings. Leave the role links completely configured and do not worry about removing the send ports from the BizTalk parties.

5. Delete the receive ports from the BizTalk application.

6. Deploy from Visual Studio

7. Reimport the bindings that you exported in step 3.

8. Make sure everything looks correct in the Configure window. The bindings should be rebound successfully.

9. Start the BizTalk application again.

 

This is a mysterious problem that was not solved by rebuilding or cleaning my BizTalk solution. At this point it may be a BizTalk deployment bug. The benefit of the workaround is that you do not need to unconfigure the role links themselves or modify the send ports specified for each party. With one role link you could have many configured parties so this workaround could save you a significant amount of time during deployment.

 

Thanks,

I am very excited to say that I was recognized as a Microsoft MVP (http://mvp.support.microsoft.com/) for BizTalk today. I was nominated for this award for my contributions on the MSDN forums. This is a huge honor for me. I am glad to be a member of the BizTalk and Connected Systems communities and look forward to the new opportunities that this award will bring.

 

Thanks,

A few months ago I had posted on a way to run both Visual Studio 2008 and BizTalk Server 2006 R2 with Visual Studio 2005 so that you could take advantage of the new environment for developing WCF and WF projects while continuing to work with BizTalk. I had setup this software combination on my local development box in Windows XP and had been working fine without any significant issues. Although there are a few known issues about TFS compatibility across solutions that include BizTalk projects as well as .NET 3.5 projects, I had not encountered any issues with my BizTalk installation until today.

 

During some development I realized that I likely needed to do a repair on my BizTalk installation due to an error I was receiving about a key violation error in one of my BizTalk databases. It is a common recommendation that the installation be repaired through the Add/Remove programs repair part of the installation wizard when certain issues are encountered with BizTalk. So I loaded the BizTalk installation image and started the repair and everything worked fine until near to the end of the repair. Then all of a sudden I started receiving errors about the WCF adapters that are included with BizTalk Server 2006 R2. Here is an example error:

 

Failed to add adapter: WCF-NetNamedPipe, Error C0C025CA

 

A similar error occurred for nearly every one of the R2 WCF adapters with the name of the adapter being the only thing different in the error message. I clicked OK for every one of the error messages and this was apparently not a significant-enough error because it did not prevent the repair from continuing. The repair completed successfully without any other reports of problems. I checked in the Event Viewer and there were not any additional error messages to provide more information on what went wrong.

 

At this point I think the reason this problem occurred is because I have a version of Visual Studio on my system that is not currently compatible with BizTalk because I know that the BizTalk repair wizard did not have this problem running before I had installed Visual Studio 2008 side-by-side. Until I find more information about this error I have to say this is a known issue for BizTalk development where you have a side-by-side of Visual Studio 2005 with the BizTalk extensions and a separate Visual Studio 2008. At this point I am not sure whether the WCF adapters have suffered any sort of actual casualty by the repair wizard issues. But I think if you want to run the repair wizard successfully without any issues, be sure to uninstall Visual Studio 2008 prior to starting the repair wizard. Good luck with your BizTalk environment configuration!

 

I am working on a project where I am extending a BizTalk application so that I can route messages from a single FILE port to different BizTalk applications and various orchestrations through the use of role links. Role links are one of the less frequently used and discussed tools of an experienced BizTalk developer. Getting my mind around role links was difficult because of the number of configuration details required to setup a role link. I am indebted to Eric Stott's blog post at http://blog.biztalk-info.com/archive/2006/10/25/BizTalk_Role_Links_explained.aspx, but I think it would seem easier to describe role links as a precursor to the WCF binding architecture. I will describe some architecture notes on role links next but to see my thoughts on how role links are like WCF, skip on down a few paragraphs.  

 

Role links are essentially the opposite of dynamic ports. A dynamic port consists of one port that then has multiple variations of configurations based on the dynamic expressions applied to this port for one adapter. A role link is a static configuration but is essentially a collection of ports of different adapters. For a dynamic port there is not always an explicit party concept; in a dynamic port a message is being sent from one organization to some other variable organization. And in a role link a message is either being sent or received and is then one of a collection of organizations for each endpoint. Role links are based on ports that are designated as being associated with some BizTalk party. The party can have one or more aliases which act as destination tokens when used with role link based routing.

 

So here are a few examples of how role links could be used:

- When you need to have dev, test, and production parties all hosted in a single BizTalk application and merely need to differentiate logic for these parties based on ports

- When you need to expose a single business process to multiple parties without creating more than one orchestration to handle all of them.

- When you need more than 1 backup port in a BizTalk application

- When you are struggling to manage so many BizTalk artifacts and are looking for a way to reduce the number of orchestrations

- When you want a lightweight built-in BizTalk routing mechanism that has a simple but flexible mechanism for specifying a routing destination.

- When you want to expose more than one adapter for some organization as connectivity options

 

This last example of a role link use helped me think about a more contemporary example of a role link. In WCF when you expose a service, it is possible to expose more than a single endpoint for a service so that the organization that connects to the service can use either HTTP or TCP or some other protocol or set of binding options to connect to the service. This is frequently used to expose a WSI basic profile service for interoperability and a WS-* or SOAP 1.2 service for newer clients as in the following example: http://keithelder.net/blog/archive/2008/01/17/Exposing-a-WCF-Service-With-Multiple-Bindings-and-Endpoints.aspx. WCF provides the binding infrastructure so that it is possible for a client to have various options for connectivity. In the same way, role links provide a way to provide to a client multiple integration options that all get consumed into the same business process.

 

So role links are very similar to the binding infrastructure in WCF due to the trasnport-level flexibility both enable. If you are a BizTalk developer, you should take role links for a spin because they provide some very valuable extensibility options. It does take a while to get used to role link abstractions so I recommend taking WCF as a good example of what role links seek to accomplish and then throw adapters and parties in the mix and it makes much more sense. Have fun with role links!

Recently I worked on a BDC project and I was able to get a taste for what is possible for out-of-the-box SharePoint integration with external systems. The tricky aspect of BDC web service integration is that it only works with older web services - ones that use Basic Profile. The challenge of this is that you are limited in the integration options that are available because you must rely on the older SOAP and HTTP protocols. There are Office SDK samples that showcase integration with SAP and Siebel over BDC web services or Oracle over BDC database connections, but I was wondering how I would expose PeopleSoft or some other enterprise system that cannot be reached with a Basic Profile web service or a database connection.

 

Based on my BizTalk background, I knew that I could use the PeopleSoft adapter through BizTalk and then expose a BizTalk orchestration as a Basic Profile web service that could then be consumed by the BDC. So essentially this would be like a BDC bridge over BizTalk. This is when I started exploring the WCF LOB SDK as an interesting technology because the BizTalk adapter pack 1.0 is based on this SDK and it basically provides enterprise integration over WCF. Very few people are playing with the WCF LOB SDK, mostly because I think it seems confusing and has a poor name. The SDK is targeted at BizTalk developers with its "adapter" terminology, but it is really more of a way to talk about BDC from an adapter-oriented mindset. The LOB terminology should connect in your mind the LOB aspects of the BDC. If you look at the BDC documentation, the concepts are all about connections rather than adapters, but the WCF LOB SDK redefines the adapter concept by describing it as part of the channel infrastructure. So the future for the BDC is a redefined adapter based in WCF.

 

I found a few links that tie together the BDC adapters <-> WCF LOB SDK concepts, which really give us a glimpse of how the WCF LOB SDK is going to actually become more valuable from a MOSS perspective as the MOSS product is enhanced to support WCF. Usage pattern 7 on http://blogs.msdn.com/biztalk_adapter_development/archive/2007/07/09/wcf-lob-adapter-usage-patterns.aspx mentions how future BDC enhancements will rely on the WCF LOB SDK. It looks like Jesus Rodriguez also played with some of these scenarios: http://weblogs.asp.net/gsusx/archive/2007/07/12/sharepoint-bdc-wcf-adapters-and-more.aspx. The dates on these links suggest these concepts are old news but it is still relevant because the BizTalk adapter pack 1.0 was only recently released. The plans for this SDK go way back but the vision for how it will be used in the future seem to be missing. The WCF LOB SDK gives us a vision for the future of the BDC, which should include having a better designer experience for BDC developers as well as a much richer level of connectivity and flexibility. So the ROI for the WCF LOB SDK is still down the road, but once MOSS can support WCF it will be possible to integrate these other enterprise systems like PeopleSoft straight from the BDC. This is an exciting vision for the future of the BDC technology and a great reason to get your hands dirty with the WCF LOB SDK.

 

Thanks, 

As part of a couple projects I have gained experience working with Systems Center Operations Manager (SCOM) as part of working with IT departments who needed to manage BizTalk, Active Directory, and SQL Server. One request I recently got was to determine how SQL actually gets executed as part of the SQL Server management pack. So the first thing I needed to do was to load the SQL Management pack into SCOM and then explore the Task artifacts to determine which ones were executing SQL. You would think that there would be quite a few of them for DBA-centric tasks. Along the way of doing this I noticed a few gotchas and wanted to point these out. Catch the end of this post to see the results of my probing into the management pack.

 

When installing the SQL management pack download, an MSI runs to export a copy of all of the management pack files (*.mp) that need to be loaded into SCOM. If you search on the management pack catalog (http://technet.microsoft.com/en-us/systemcenter/cc462790.aspx), it comes back with the same management pack for SQL 2000, 2005, and 2008. But when running the installer it does not created any 2008-specific management packs, only 2000 and 2005 ones so this is a little misleading. The MSI creates 5 different management pack files, the SQL Core Library, the 2000 discovery pack, the 2000 monitoring pack, the 2005 discovery pack, and the 2005 monitoring pack. The core library should be loaded first, then one or both discovery packs, and finally one or both monitoring packs. Most managment pack installs just create a single .mp file so be sure to run the additional packs to get all of the management artifacts!

 

I loaded the management packs into SCOM and then started searching for where SQL may be executing based on the SQL management pack. There are a couple of different things to look for in a management pack such as alerts, monitors, tasks, rules, etc. Typically tasks are used for executing scripts, starting Windows services, or other command-line executable tasks. I ended up only finding a single instance of specific SQL being executed from the SQL Management pack:

sqlcmd.exe -E -S $Target/Property[Type="SQL!Microsoft.SQLServer.DBEngine"]/ConnectionString$ -Q "sp_configure"

 

One thing that is interesting about this command is that it pulls out a property from for the connection string in what looks like a Powershell variable and then executes a call to sp_configure. This provides an example of what would be required to create a custom task that executes SQL. Only finding a single task was surprising - I was expecting that there would be one for updating indexes based on the results of SQLProfiler, or other common administration tasks. Alas, I will need to create a custom management pack to do these kinds of things. At least this example shows me how easy it would be to create a task to execute some custom SQL.

I recently uncovered an overflow problem in BDC columns. When returning data from a BDC data source, it is possible to return too much data if the BDC is being used as a column in a custom list. The maximum length of a BDC column when used in a list is 255 characters, which is the default maximum length of a text type column in MOSS. When using the BDC web part, data that is returned is not subjected to the maximum length of 255 characters. When more data is returned than 255 for a particular column, I received the error: "Invalid text value". Within the BDC application definition, the TypeDescriptor data type for these columns was System.String. I could not find any other errors in the event logs or SharePoint logs for this problem.

 

I wanted to post this maximum length because the MSDN documentation does not mention this.

 

Since I was working with a SQL Server BDC connection, I was able to wrap fields that could overflow the 255 character maximum using the substring function as in substring(<fieldname>,1,255), but this will not be possible for all BDC connections.

 

Thanks,

I have been recently working on a MOSS BDC project and have been struggling with some ambiguous error messages that were occuring on when using BDC data columns in a custom list. The error I was receiving was:

   An error occurred while retrieving data from <BDC Database>. Administrators, see the server log for more information.

 

After checking the event viewer logs I realized this error message actually refers to the SharePoint logs in the 12 hive\Logs. After some investigation of the log I realized that my BDC application definition was expecting an Int64 value to be returned when in fact it was receiving an Int32. From a programming perspective it would seem that any Int32 value should fit within an Int64. The other way around should cause an overflow. The BDC complained like the Int64 data type resulted in a data type mismatch.

 

This problem illustrates that implicit conversions when using the BDC are not provided by the MOSS runtime, so it is VERY important to be sure that the type descriptors in the BDC application definition match exactly what is returned from a database or web service. I am sure there are other cases where a developer would expect an implicit conversion to occur but will not with the BDC.

 

When using the BDC Meta Man tool or the Microsoft BDC Editor, watch out for definitions that default to Int64 as the data type for a column because this could result in an error if an Int64 is actually not being returned. This is one very tricky issue in BDC development.

 

Thanks,

I was recently working on a SharePoint Business Data Catalog (BDC) project and needed to configure the single sign-on (SSO) functionality of SharePoint. For anyone not familiar with the SSO functions in BizTalk or SharePoint that I am talking about, I will explain it briefly. I am not speaking about SSO in the sense that a website or application user logs in once and only once. I am speaking about SSO functionality in these two server products which enables the secure storage of credentials used for connecting to external systems. There are two types of SSO - authorization and impersonation. So that everyone knows, I am talking about the SSO impersonation capabilities of these two server products.

 

Coming from a BizTalk background I had worked with SSO in BizTalk quite a bit but was not initially prepared with how different the configuration was for SharePoint. This post will attempt to do a thorough comparison of all of the SSO functionality between the BizTalk and SharePoint products.

 

Feature	BizTalk	SharePoint
Configuration Interface	Command-Line and BizTalk Configuration Wizard	SharePoint Central Administration website
Encryption Key Management	Possible only via Command-Line	Possible only via website function "Manage Encryption Key"
Encryption Key Backup	Command-Line File Based, Can backup to any file location	Website Based, Can backup only to a removable drive
SSO MMC Capability?	Exists as an MMC, can be loaded remotely with appropriate BizTalk install	MMC does not exist, cannot be configured remotely.
Requires RDP access for SSO configuration?	No	Yes
Group Setup for SSO	SSO Administrators, SSO Affiliate Administrators, SSO Service Account	SSO Administrators, SSO Administrator Account, SSO Service Account
Requires SSO Service Account to be process identity for configuration?	No	Yes
SSO delegation options	Credential based on port configuration	Credential variability options - Group and Individual options.
Runs as Windows Service?	Yes	Yes
Required on all servers in Farm?	No	Yes
Operates out of an SSO database?	Yes	Yes
Command-line options for SSO?	Yes	Partial
Automatic Credential Update?	Yes	No