...by Daniel Szego
"On a long enough timeline we will all become Satoshi Nakamoto.."
Daniel Szego

Thursday, July 10, 2014

Connecting SAP employee information with SharePoint user profile services - Part 2

In the first part of the blog, we have seen how the basic connection with SAP can be set up :
Connecting-SAP-employee-information with SharePoint user profile services Part 1 
Now let we see how the BCS side can be configured :
STEP 2 : Creating BCS. 
1. Create an empty SharePoint 2013 Project in the same solution add a Business Data Connectivity Model to the Project and do not forget to refernce the project of the old command promt application, fo course ony after that you set the project output type to class library :

2. configure in the package, that both the solution dlls and the SAP Connector dlls are deliverd to the GAC (rscp4n.dll, sapnco.dll, sapnco_utils.dll)
3. Add a static Connection class to have a reference on the connection itself :​
    public static class Connection
        public static RfcDestination rfcDest = null;

        static Connection()
            SAPSystemConnect sapCfg = new SAPSystemConnect();
            rfcDest = RfcDestinationManager.GetDestination("XXX");

4. Update the employee to cover the basic properties.
   public partial class EmployeeEntity
        public EmployeeEntity(Employee emp)
            this.Job = emp.Job;
            this.Name = emp.Name;
            this.Organisation = emp.Organisation;
            this.PeronalNr = emp.PeronalNr;
        public EmployeeEntity()
        private string _PeronalNr;
        private string _Name;
        private string _Organisation;
        private string _Job;
        public string Name
                return _Name;
                _Name = value;
        public string Organisation
                return _Organisation;
                _Organisation = value;

        public string Job
                return _Job;
                _Job = value;
5. Implement the finder and specific finder methods to get the employee infromation:
      public static EmployeeEntity ReadItem(string Name)
            Employee e = new Employee();
            EmployeeEntity ret = new EmployeeEntity(e);
            // get all Basic employee information from SAP

            List<Employee> emps = Employee.getAllEmployees(Connection.rfcDest);
            foreach (Employee emp in emps)
                    ret = new EmployeeEntity(emp);
                if (emp.Name.ToLower().Contains(Name.ToLower()))
                    ret = new EmployeeEntity(emp);
                if (emp.Name.ToLower().Equals(Name.ToLower()))
                    ret = new EmployeeEntity(emp);
            return ret;
       public static IEnumerable<EmployeeEntity> ReadList()
            List<EmployeeEntity> ret = new List<EmployeeEntity>();
            // get all Basic employee information from SAP

            List<Employee> emps = Employee.getAllEmployees(Connection.rfcDest);
            foreach (Employee emp in emps)
                ret.Add(new EmployeeEntity(emp));
            return ret;

In this example specific finder is simply the name of an employee, that is pretty much general configured to match. We certainly use the Employee wrapper class that was implemented in the first part of this blog to capture the SAP information, 
6. Configure BCS either with the help of the editor or directly the xml to contain the properties and the Name property as an indexer :

7. After building and deploying, you can test if it works as well, like creating an external list in one of the sites and populating SAPUserProfil EmployeeEntity external type (do not forget giving rights to the external content type at the central administration) :

STEP 3: So, last but not least, the external content type has to be configured in the user profil sync: 
1. In the User Profile Service Application by Configure Syncronisation Connection create a new connection from the type Business Data Connectivity, and configure a matching based on the AccountName or PreferedName (basicaly the specific finder is pretty flexible configured, so all of these conbinations should work). In this example we already have one connection to syncronise the basic accounts and user related information from Active Directory.

2. Create two new properties in the user profile service application for SAP Job and SAP Organisation infromation (like SAPJob2 and SAPOrg2) and configure them to get the information from the newly configured syncronisation connection. 

3. Start a full User Profile syncronisation. 
4. Check if the user profile has got the information in the extended properties, like 


The end. 

Connecting SAP employee information with SharePoint user profile services - Part 1

Some companies use both SharePoint and some kind of a SAP system and in the internal IT landscape. The problem is very often that some user specific information is stored both in AD and imported to SharePoint with the help of user profile sync and the same data is stored and managed on the SAP side as well. This article demonstrates how you can directly integrate SAP employee specific data with SharePoint.
Reaching the goal we need to do actually three major steps:
  • STEP 1. Pull out the information from SAP.

  • STEP 2. Integrate with BCS
  • STEP 3. Configure BCS with the user profile sync of SharePoint.

  • STEP 1: you have many options to do that depending on what kind of an environment you have and what kind of a programming experience. Some of the possible examples:
     If you have Netweaver than you can get your data with the help different web services and put it to    a BCS class event with SharePoint designer.
  • Without Netweaver you should use some kind of a connector, like the SAP .NET Connector (https://help.sap.com/saphelp_nw04/helpdata/de/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm) or the ERP connect from Theobald (http://theobald-software.com/de/index.htm),
  • If you are brave enough and do not afraid of deep dive dll hacking then you can use the original communication interface from SAP: librf32.dll.

  • In this scenario we use the SAP.NET connector, so let we create for the first run a simple console application that read out the information from SAP:
    1.  Create a Visual Studio project, a console application for the first run, name it like Test and add a reference to the SAP .NET connector dlls (rscp4n.dll, sapnco.dll, sapnco_utils.dll).
    2 Set the SAP configuration parameters by implementing the IDestinationConfiguration​ interface  and set the ​SAP connection parameters like AppServerHost, SysteNumber and a user credential to communicate. 
    public class SAPSystemConnect : IDestinationConfiguration
            bool IDestinationConfiguration.ChangeEventsSupported()
                return true;
            public event RfcDestinationManager.ConfigurationChangeHandler ConfigurationChanged;
            RfcConfigParameters IDestinationConfiguration.GetParameters(string destinationName)
                if ("XXX".Equals(destinationName))
                    RfcConfigParameters parms = new RfcConfigParameters();
                    parms.Add(RfcConfigParameters.AppServerHost, "XXX");
                    parms.Add(RfcConfigParameters.SystemNumber, "XXX");
                    parms.Add(RfcConfigParameters.User, "XXX");
                    parms.Add(RfcConfigParameters.Password, "XXX");
                    parms.Add(RfcConfigParameters.Client, "XXX");
                    parms.Add(RfcConfigParameters.Language, "XXX");
                    parms.Add(RfcConfigParameters.PoolSize, "XXX");
                    parms.Add(RfcConfigParameters.MaxPoolSize, "XXX");
                    parms.Add(RfcConfigParameters.IdleTimeout, "XXX");
                    return parms;
                return null;

     3. create an Employee wrapper class to capture the required properites of employee. We use Name, Organisation and Job as simple strings in this example. Having finished implement a GetAllEmployee function to read out infromation from SAP and create a list of Employee wrapper objects.
    public class Employee
            public string Name;
            public string Organisation;
            public string Job;
            public static List<Employee> getAllEmployees(RfcDestination destination)
                List<Employee> ret = new List<Employee>();
                RfcRepository repo = destination.Repository;
                IRfcFunction employeeList = repo.CreateFunction("BAPI_EMPLOYEE_GETLIST");
                employeeList.SetValue("ORG_SEARK", "*");
                employeeList.SetValue("JOB_SEARK", "*");
                employeeList.SetValue("SUR_NAME_SEARK", "*");
                employeeList.SetValue("LST_NAME_SEARK", "*");
                IRfcTable employees = employeeList.GetTable("EMPLOYEE_LIST");
                for (int cuIndex = 0; cuIndex < employees.RowCount; cuIndex++)
                    employees.CurrentIndex = cuIndex;
                    Employee emp = new Employee();
                    emp.Name = employees.GetString("ENAME");
                    emp.Organisation = employees.GetString("ORG_TEXT");
                    emp.Job = employees.GetString("JOB_TEXT");
                return ret;
    The prvious code expects a preconfigured destination that represents the SAP connection. Then calls the BAPI_EMPLOYEE_GETLIST BAPI with the some imput parameter settings (practicaly setting * as wildchar for the ORG_SEARK, JOB_SEARK, SUR_NAME_SEARK and LST_NAME_SEARK input parameters). 
    After calling the BAPI the EMPLOYEE_LIST result table is iterated and the necessary output values are read out. Anyway if you feel unconfident with the parameter names of the BAPI, then start SAP GUI, start the se37 transaction and test the BAPI with different input output variable conbinations. 
    3. Test the program from the console application by creating a  destination and calling getAllEmployees static procedure.
     static void Main(string[] args)
                SAPSystemConnect sapCfg = new SAPSystemConnect();
                RfcDestination rfcDest = RfcDestinationManager.GetDestination("K47");
                List<Employee> emps = Employee.getAllEmployees(rfcDest);
                foreach (Employee emp in emps)
                    Console.WriteLine(string.Format("Employee {0} - {1} - {2}", emp.Name, emp.Job, emp.Organisation));

    Having finished, you should see a list of employee names and the regarding job and organisation information on the one hand as a result of the console application :

    On the other hand you can test if the data is actually the same in SAP, as an example by starting the se37 tranaction and calling the BAPI_EMPLOYEE_GETLIST with the same '*' input values : 

    In the next part of this blog, we integrate the exsiting project into a BCS Project and add to the SharePoint user profile sync.

    Integrating K2 with SAP with K2 Connect

    So, it was a challenge to see how one can integrate K2 with SAP without K2 Connect. Now let we see ho the same process can be realised with the help of K2 Connect. Prerqisit : You need to have K2 Blackpearl, K2Smartforms, K2 Connect and an SAP System instlled not necesarily with Netweaver but with a Controlling module.So for the first run start Visual Studio, create a new K2 Project called K2ConnectTest and add a new K2 Connect Service Object called K2ConnectTest as well to the project :

    Having finished open in the VS Toolbox the K2 Service Object Designer and start a new filter criteria with "*costcenter*", after that choose the BAPI_COSTCENTER_GETLIST Bapi, tes it with the help of the K2 connect Test Cockpit at loading the interface then testing the interface firstly by adding 1000 default value to the controlling area and leaving the other values as default. 

    If everything succeed and you get the result value in the costcenter list. then add a new service, name it as Costcenter, to the Visual Studio project and add the BAPI_COSTCENTER_GETLIST to the Costcenter service :

    Having finished, build the VS projects and say Publish Service Object. If everything runs without error then open the Smart Object Service Tester application, the K2Connecttest should be published under K2 Connect Service :


    As a next step create a Smart Object from the K2ConnectTest Smart Object Service. After that test it explicitely by adding 100 to the ControllingArea input field:

    As a last step create a View and a Smart Form from the Smart Object, for the first run just simple pressing the generate view button on the Smart Object. For the first run you will probably get an error message it is because the ControllingArea input field is not defined. So ​​open the autogenerated View, go to the Initialisation rule and set the ControllingArea to 1000 for the first run:

    If everything goes well, then after running either the form or directly the view you should get the information of the list of costcenters to the 1000 controlling area:

    Conclusions : 
    - You can integrate SAP with K2 with or without K2 connect. 
    - If you do it without K2 Connect you must have development skills in C# and some direct SAP know how. 
    - If you do it with K2 connect you do not necessarily must know to code or know how to handle SAP, however you have to have a strong IT Skill. 
    - If you do with K2 Connect, you have to pay the K2 connect license (as I know around 20k).
    - If you do  without K2 Connect, you have to have one connector (as I know Theobald is around 1k pro year, if you manage to hack lib32rfc.dll it is as I know free of charge)
    - There is no best solution, just an optimal for a certain customer :)

    The end.

    Integrating K2 with SAP (without K2 connect) - Part 2

    In the first part of this blog we set up the envrionment and started to implement the first class for a custom smart object serivce to make an integration with SAP. 
    Let we just continue with the rest of the coding. Let we just overwirte DescribeSchema : it implements which properties and which methods are published from the given smart object service :​

    public override string DescribeSchema()
        //set base info 
        this.Service.Name = "SPEGSAPService";
        this.Service.MetaData.DisplayName = "SPEGSAPService";
        this.Service.MetaData.Description = "SPEGSAPService";

        //Create the service object, one to many 
        ServiceObject so = new ServiceObject();
        so.Name = "SPEGSAPServiceObject";
        so.MetaData.DisplayName = "SPEGSAPServiceObject";
        so.MetaData.DisplayName = "SPEGSAPServiceObject";
        so.Active = true;

        //Create field definition for CostCenter ID
        Property propertyCostCenterID = new Property();
        propertyCostCenterID.Name = "CostCenterID";
        propertyCostCenterID.MetaData.DisplayName = "CostCenterID";
        propertyCostCenterID.MetaData.Description = "CostCenterID";
        propertyCostCenterID.Type = "System.String";
        propertyCostCenterID.SoType = SoType.Text;

        //Create field definition Cost Center Name
        Property propertyCostCenterName = new Property();
        propertyCostCenterName.Name = "CostCenterName";
        propertyCostCenterName.MetaData.DisplayName = "CostCenterName";
        propertyCostCenterName.MetaData.Description = "CostCenterName";
        propertyCostCenterName.Type = "System.String";
        propertyCostCenterName.SoType = SoType.Text;

        //Create method
        Method method = new Method();
        method.Name = "Load";
        method.MetaData.DisplayName = "Load";
        method.MetaData.Description = "Load custom service data";
        method.Type = MethodType.List;

        return base.DescribeSchema();

    In the previous example we achived that the smart object instances of this type will publish two properties, named CostCenterID and CostCenterName, and there is gonna be one method, a list type, that reads out of list of these properties.   Let we now override the main Execute method that is called when any of the methods of the smartobject is called :
    public override void Execute()
            foreach (ServiceObject so in Service.ServiceObjects)
                switch (so.Name)
                    case "SPEGSAPServiceObject":
                        foreach (Method method in so.Methods)
                            switch (method.Type)
                                case MethodType.List:
                                    ReadSPEGSAPService(so, method);

    The previous method simply iterates over possible service objects and methods, and chooses practicaly the only one that has been implemented and calls ReadSPEGSAPServices to read out the data from SAP. This function is acually responsible for for the SAP connection, it initiates a R3 connection with a given connection string to an SAP system. After that it creates a function to the BAPI_COSTCENTER_GETLIST BAPI; input parameters are set; with function.Execute() is the BAPI carried out; at the end the COSTCENTER_LIST table is iterated and costcenter code and name are read out.  Putting the result back is realised with the help of the ServiceObject property and table functions. Please note that BAPI structure is the easiest to discover with the help of the se37 transaction, described at the first part of this blog.

    private void ReadSPEGSAPService(ServiceObject so, Method method)
        string connstring = "ASHOST=... SYSNR=... USER=... PASSWD=... LANG=... CLIENT=...";
        var connection = new R3Connection(connstring);


            var function = connection.CreateFunction("BAPI_COSTCENTER_GETLIST");
            function.Exports["CONTROLLINGAREA"].ParamValue = "1000";
            var table = function.Tables["COSTCENTER_LIST"];

            if (table != null || table.Rows.Count > 0)
                var c = 0;
                foreach (RFCStructure row in table.Rows)
                    if (c++ > 100)

                    var costcenterid = (string)row["COSTCENTER"];
                    var costcentername = (string)row["COCNTR_TXT"];

                    so.Properties["CostCenterName"].Value = costcentername;
                    so.Properties["CostCenterID"].Value = costcenterid;
            if (connection != null && connection.Ping())

    Last but not least, overwrite the Extend method with an empty procedure, than compile and build the project. 
     override void Extend()
    STEP 4: Deployment. To deploy the project firstly copy the created dll and the ERPConnect45.dll as well to the folder of the service type dlls.  C:\Program Files (x86)\K2 blackpearl\ServiceBroker.Open SmartObject Service Tester (located in C:\Program Files (x86)\K2 blackpearl\Bin) and register the new service type.

    Create a new service instance from the service type; here you can set the ControlAreaNumber configuration property as it was shown on the screenshot in the first part of this blog. Having created the instance create some SmartObject from the type and test them with the helo of SmartObject Service Tester. If everything went fine, you should get a list of costcenter IDs and Names after pressing execute on the test window. Please note that if something went wrong then the easiest way to find a bug is with the help of the Visual Studio Debugger. You should Attach to the K2HostServer.exe and you can debug your code.

    If everything works, the smartobject that you created is ready for being used either in K2 workflow or in Smartforms. As an example the following screenshot demonstrates the simpleest Smartform generated to show the result.

    The end.

    Integrating K2 with SAP (without K2 connect) - Part 1

    ​So, ever wanted to integrate your K2 envrionment with SAP ? Well actually you have some possibilities. The first option is certainly K2 connect that is meant to provide a tool to easily configure data from SAP as smart objects. If you do not have or you do not want to use K2 connect, one option is certainly to make an integration via the web services of SAP integrating either with Endpoints WCF or Endpoints Web services smart object service.
    However you need to have a version from SAP Netweaver and publish some web services. So what happens, if you do not want to use K2 connect and you want to do the integration without web services and netweaver for some reasons, with the help of the old fashioned BAPI integration.  Well, it is possible, but you need deveopers. We demonstrate in the followings how :
    Firtly you need of course Visual Studio 2010 or 2012 and an SAP connector. 
    Among the other you can consider the following options: 
    - ERP Connect from Theobald : 
      You can download a trial version but buying a final version is also not so expensive. 
    - SAP .Net connector :
      You can download the product from the SAP Marketplace. Unfortunately if you are not an SAP Partner,     it might be a little bit difficult.
    - Reverse engineering librfc32.dll :
      At installing the SAP GUI, a librfc32.dll is installed as well, theoreticaly it is capable of doing the SAP     connection, I mean of course if you are good ar reverse engineering. 

    In the following, we use ERP Connect from Theobald. 
    In the following use case we want read out a list of cost center names and cost center codes for a given controlling area code.  

    STEP 1: Choose and test a BAPI.
    Reading out cost cetner information is possible with the help of the BAPI_COSTCENTER_GETLIST Bapi. 
    You can directly test the BAPI by starting se37 transaction.

    with Display you can see the code and with "Test/Execute" you can diretly test the BAPI with different input parameter conbination. 

    STEP 2: Open Visual studio and create an initial project producing a class library on framework 4.5.

    Three important things however: 
    - Install ERP Connect on the development platform and do not forget to reference the ERPConnect45.dll
    - Add reference for the SourceCode.SmartObjects.Services.ServiceSDK.dll, it can be found under C:\Program Files (x86)\K2 blackpearl\Host Server\Bin\
    - Install the librfc32.dll exactly in a way as it is described in (otherwise you well get hot error messages in the future):
    Configure librfc32.dll on an 64 bit environment​
    STEP 3: Make code. 
    The major point gonna be to implement a custom smartobject service, you can have a basic approcach for example from the following link:
    create a descendent class from the ServiceAssemplyBase : it is the parent class for all custom SmartObject serv​ice.

    public class SPEGServiceBrokerClass : ServiceAssemblyBase
         public SPEGServiceBrokerClass()
    override the GetConfigSession procedure and set the properties that you need : ConfigSession contains the possible parameters of SmartObject Service Instance that you can set for instance  in the SmartObject Services Tester Program. See the following screenshot :

    In our example we only add on such a property that is the ControllingAreaNumber

    private ServiceConfiguration _serviceConfiguration;

    public ServiceConfiguration ServiceConfig
        get { return _serviceConfiguration; }
        set { _serviceConfiguration = value; }

    // configuring the basic properties
    public override string GetConfigSection()
        return base.GetConfigSection();

    ​To be continued in Part 2 

    Reading out SharePoint list items from SAP ABAP

    So, I know the usual way is to read out SAP data from SharePoint, but for the sake a variety let us see the reverse direction, reading out SharePoint List items and information from SAP. So, what you have to have is an SAP environment and a developer user. For example you can download and install one of the NetWeaver trial environment from scn.sap.com. Let we assume that we want to read out the elements of a SharePoint list called MyList, including 2 columns to read out : Title an​d PropertyX.

    1. Log on to SAP

    2. Start APAB Development workbench with /ose80

    3. Add a new Program to the local objects called for instance     Z_SP_DEMO_GET_LIST_ITEM

    4. Add local variable for managing urls :

    5. Add local variables for managing result as string:
       DATA: w_string TYPE string,
          w_result TYPE string,      r_str TYPE string,w_result_trimmed TYPE string,      result_tab      
          TYPE TABLE OF string.

    6. Add reference variable to http_client :
       DATA: http_client TYPE REF TO if_http_client.

    7. To read out the elements of a list, you should use a REST web service from SharePoint _api/web/lists/GetByTitle() with special parameters to select column information of the items of the list. The two elements are set in SEND_STRING1 and SEND_STRING2 and they are concatenated in SEND_STRING.   ​
     SEND_STRING1 = 'http://sp13Dev/_api/web/lists/'.
     SEND_STRING2 ='GetByTitle(''MyList'')/items?      

    8. Create the http object by create_by_url constructor:
      CALL METHOD cl_http_client=>create_by_url
        url = SEND_STRING
        client = http_client  
       EXCEPTIONS    argument_not_found = 1    plugin_not_active  = 2    
        internal_error     = 3
        others             = 4.
    9. Disable authentification popup dialog :
      http_client->propertytype_logon_popup =        

    10. Set Authentification : Username and password. (Please note that in this simple example the SharePoint authentification is set to basic and only on HTTP. More complicated and secure authentification mechanism can be imagined as well however they require additional configuration, like importing SSL certificate for HTTPS.)
       call method http_client->authenticate
       ( username = 
    'username' PASSWORD = 'psw' ).​

    11. Send the request :
      CALL METHOD http_client->send
         http_communication_failure = 1         
         http_invalid_state         = 2.

    12. Receive the result :
      CALL METHOD http_client->receive    
        http_communication_failure = 1         
        http_invalid_state         = 2        
        http_processing_failed     = 3.

    13. Check if there was no error :
          if sy-subrc = 0.

    14. Get the result xml as one big string into w_string:
       w_result = http_client->response->get_cdata( ).

    15. The result at the moment is one huge xml file containing the Title and PropertyX column information as <d:Title> and <d:PropertyX> tags. You can either make some XML processing or just some simple ones to cut the necessary values of the two previously mentioned tags. We do in the following just a simple text transformation to cut out the basic values.​
       write: /'Title ', 'PropertyX'.
       REFRESH result_tab .
       SPLIT w_result AT '<' INTO TABLE result_tab .
         loop at result_tab into w_result.
           if w_result cs 'd:Title>' and not ( w_result cs
             '/d:Title' ) .          
               write :/ w_result+8 .       
            endif.      if w_result cs 'd:PropertyX>' and not ( w_result
             cs '/d:PropertyX'  ) . 
               write : w_result+12 .       

    16. Check the program with CTRL F2 and then run with direct processing. If everything works without error you should get the list item values on the screen.

    17. The end. You can celebrate, drink beer :)