Calling a SAP PI Web Service (using WCF bindings) from a SharePoint 2007 Workflow

There are 2 rules when calling a SAP PI web service from a SharePoint workflow:

1. You have to use custom bindings to get authentication to work.
2. After you've added your web service via the "Add Service" dialog, you have to copy the all of the contents under into the SharePoint web.config - as per http://blogs.msdn.com/sharepointdesigner/archive/2008/11/02/calling-a-wcf-service-from-a-sharepoint-workflow-using-visual-studio.aspx. This is because the contents of app.config are (obviously) not accessible by the SharePoint Workflow runtime - you have to use the web.config instead.

Point 1 - Custom Bindings

If you try to call a web service in SAP PI which requires credentials, none of the normal authentication methods seem to work. You will always get the following error in WCF.

The HTTP request is unauthorized with client authentication scheme 'Anonymous'.
The authentication header received from the server was 'Basic realm="XISOAPApps"

To fix this, you need to use the following custom bindings rather than the normal basicHttpBinding, like so:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <system.serviceModel>
    <bindings>
      <customBinding>
        <binding name="CustomHttpTransportBinding">
          <textMessageEncoding maxReadPoolSize="64" maxWritePoolSize="16"
              messageVersion="Soap11" writeEncoding="utf-8">
            <readerQuotas maxDepth="10000000" maxStringContentLength="10000000"
                maxArrayLength="67108864" maxBytesPerRead="65536" maxNameTableCharCount="100000" />
          </textMessageEncoding>
          <httpTransport authenticationScheme="Basic" bypassProxyOnLocal="false"
              hostNameComparisonMode="StrongWildcard" keepAliveEnabled="false"
              proxyAuthenticationScheme="Basic" realm="XISOAPApps" useDefaultWebProxy="true" />
        </binding>
      </customBinding>
    </bindings>
    <client>
      <endpoint address="http://servername:50000/XISOAPAdapter/MessageServlet?channel=:INTEGRATION_SERVER_DPI:SOAPSenderPOCreate&version=3.0&Sender.Service=INTEGRATION_SERVER_DPI&Interface=http://zz"
        binding="customBinding" bindingConfiguration="CustomHttpTransportBinding"
        contract="PurchaseOrderProcessingService.PurchaseOrderProcessing_Out"
        name="PurchaseOrderProcessing_OutPort" />
      <endpoint address="http://servername:50000/XISOAPAdapter/MessageServlet?channel=:INTEGRATION_SERVER_DPI:SOAPSenderPORelease&version=3.0&Sender.Service=INTEGRATION_SERVER_DPI&Interface=http://zz"
        binding="customBinding" bindingConfiguration="CustomHttpTransportBinding"
        contract="PurchaseOrderReleaseService.PurchaseOrderRelease_Out"
        name="PurchaseOrderRelease_OutPort" />
      <endpoint address="http://servername:50000/XISOAPAdapter/MessageServlet?channel=:INTEGRATION_SERVER_DPI:SOAPSenderGOS_URL_CREATE&version=3.0&Sender.Service=INTEGRATION_SERVER_DPI&http://zz"
        binding="customBinding" bindingConfiguration="CustomHttpTransportBinding"
        contract="PurchaseOrderAttachUrlService.MI_GOS_URL_CREATE_Syn_Out"
        name="MI_GOS_URL_CREATE_Syn_OutPort" />
    </client>
  </system.serviceModel>
</configuration>

Point 2 - Web Service Configuration Settings
(from http://blogs.msdn.com/sharepointdesigner/archive/2008/11/02/calling-a-wcf-service-from-a-sharepoint-workflow-using-visual-studio.aspx)

1. Open web.config in VS for editing. You can find the file in the "\inetpub\wwwroot\wss\VirtualDirectories\80" directory, where 80 corresponds to the port of your SharePoint application.
2. Copy the "System.ServiceModel" element from app.config into web.config. If you already have a "System.ServiceModel” element, you’ll need to merge the and elements in manually.
3. To complete your changes, open a windows command window (Start/Run/cmd.exe) and type "iisreset", which will cycle the sharepoint web application, so it can pick up the web.config changes.
   
   

InfoPath 2007 - Design Checker Error "Binding a non-repeating control to a repeating field or group is not supported by InfoPath Forms Services."

For read-only purposes, I had expression boxes in my InfoPath 2007 form (browser-enabled) that referred to (via XPath) repeating items within the form - but using an XPath sum(). Even though sum always returns a single value, InfoPath (erroneously) detects the use of the repeating table (i.e. I am trying to sum the total purchase order items). It then shows the following error in the Design Checker:

Binding a non-repeating control to a repeating field or group is not supported by InfoPath Forms Services. To fix this problem, remove the control or replace it with a repeating control, such as repeating section or table."

I automatically assumed that this would be the same as other "errors" and would prevent the publication and activation of this form. This is not the case. In fact, the Design Checker should really should show as a warning, as you can safely ignore the "error" and publish anyway.

Setting Up Single Sign On for SharePoint 2007 for Integration with SAP

I'm currently integrating SAP with SharePoint for a client at the moment. One of the requirements for the set up of iView web parts is to set up SSO beforehand. Problem is, some of the articles I looked at miss some important steps or have some incorrect information. I'm not showing the full set of instructions here as it is a very long list. See http://download.microsoft.com/download/b/c/1/bc1939d9-638f-4053-b602-4258f18bb683/CB_045_Interoperability%20between%20SAP%20NetWeaver%20Portal%20and%20Microsoft%20SharePoint%20Server%202007.pdf for the comprehensive listing of steps. While this PDF file is good, see step 6 "Physically log into the box as the SSO account" as it is not mentioned in this PDF or the books I've seen. Also see my section "For the Set up of the Trusted Hosts File" for another correction to the PDF document.

For the set up of the SSO Service

1. Create a new domain service account for running SSO service. E.g. MYDOMAIN\svc_sso
2. Add that account to local admins on the box
3. Give the account system admin permissions on the SQL Box
4. Change SSO service's Identity to the new service account from the Servicesconsole (Admin Tools->Services), change to automatic start and start the service up
5. Add SSO service account as a farm administrator by using "Update farm administrator's group" link from share point central admin.
6. Physically log into the box as the SSO account (you cannot just do a Login as a different user in Central admin – it won’t work. Presumably a bug in MOSS.) NOTE THIS IS AN IMPORTANT Step (otherwise you may get errors such as “Login Failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’” or “You do not have the rights to perform this operation.” In the Manage Server Settings section of SSO setup)
7. Open up Central admin, and you should now be able to enter and set the SSO account information.Remove the account from farm admins.

For the Set up of the Trusted Hosts File
Also note that instructions say that you have to set up the trusted hosts file or the sample in
c:\Program Files\Microsoft Office Servers\12.0\Config\TrustedSAPHosts.config or
c:\Program Files\Microsoft Office Servers\12.0\Config\TrustedSAPHosts.sample.xml as per
http://technet.microsoft.com/en-us/library/cc262461.aspx

This is not correct - the file is actually in in C:\Program Files\Microsoft Office Servers\12.0\Config\

SQL Server Integration Services 2008 - Fix for when SSIS Script Tasks Freeze or Run indefinitely without Returning a Result

As you may have seen in my previous posts, I've been having some interesting adventures with SQL 2008 SSIS packages of late. However, the most obscure one hit me today which meant that all Script tasks would run endlessly without error if the code hit ANY exception at all. The problem would happen even when the exception was fully handled within a try...catch blog.

I tried several things like:

1. Remove all code except for a throw new Exception() - SSIS Script task STILL freezes
2. Change the project target back to .NET 2.0 from 3.5 (after removing all code) - SSIS Script task STILL freezes
3. Remove all variables from the Script Task properties - VOILA!
   
   I eventually worked out that a Package Level variable that i'd defined named "DBConnectionString" would stop exceptions from being bubbled up to the parent package and freeze the task when it was marked as either a Read-Write Variable and a Read-Only variable in the script task properties. I removed this variable definition from the script task properties and all exceptions started to bubble up correctly.
   
   I tried to reproduce the issue in a completely new package with no success (on the production 64 bit environment and my 32 bit dev machine). It doesn't appear to be related to the variable name or the value of that variable.
   
   This is just a warning to all those who encounter this unfortunate Script task freezing issue in SSIS. It may also have the appearance of being in an infinite loop as the whole package just sits there and will NEVER complete.

SQL Server Integration Services - How to check for the existence of an Excel Worksheet in SSIS

This code snippet shows you how to check for the existence of a worksheet in an Excel file. I had to do this because the data collection spreadsheet I was importing from would not add a $Warnings worksheet if there were no warnings for a particular set of data. The code below relies on the GetSchema() method of OLEDB connections to obtain schema information.

public class ScriptMain
{
    public void Main()
        {
            string fileToTest;
            string tableToTest;
            string connectionString;
            OleDbConnection excelConnection;
            DataTable excelTables;
            string currentTable;

            fileToTest = Dts.Variables["ExcelFile"].Value.ToString();
            tableToTest = Dts.Variables["ExcelTable"].Value.ToString();

            Dts.Variables["ExcelTableExists"].Value = false;
            if (File.Exists(fileToTest))
            {
                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                "Data Source=" + fileToTest + ";Extended Properties=Excel 8.0";
                excelConnection = new OleDbConnection(connectionString);
                excelConnection.Open();
                excelTables = excelConnection.GetSchema("Tables");
                foreach (DataRow excelTable in excelTables.Rows)
                {
                    currentTable = excelTable["TABLE_NAME"].ToString();
                    if (currentTable == tableToTest)
                    {
                        Dts.Variables["ExcelTableExists"].Value = true;
                    }
                }
            }

            Dts.TaskResult = (int)ScriptResults.Success;

        }
}

For more information, see http://technet.microsoft.com/en-us/library/ms403358.aspx#example1