Procedure to Integrate PCo with SAP MII to read Excel(.xlsx) files

[Former Member]

Recently due to a project requirement, I had to integrate PCo for reading excel(.xlsx) with SAP MII. Thought of sharing the details so that if anyone has similar requirement can easily follow the steps and achieve the same.

• Tested Environment :

           SAP MII Version :  12.2 SP6 Patch 15

           SAP PCO : PCo 2.1.5.1

          Installation Package : Microsoft Access DB Engine X32-bit

           Connector Type : OLEDB

The PCo and Microsoft Access DB Engine should be installed on the same server.

• Note : SAP MII version and PCo version is independent  and this should be configurable if the required provider exists, as to read excel(.xlsx) through PCo into MII is to have the SAP recommended provider “Microsoft Office 12.0 Access Database Engine OLE DB Provider”.

        Refer the following links for details:

        http://scn.sap.com/thread/3786499

        https://www.microsoft.com/en-us/download/details.aspx?id=13255

        http://help.sap.com/saphelp_mii140sp04/helpdata/en/4e/73a42090c5455780c43287923c1022/content.htm

• Steps :
  • Install the PCo 2.1.5.1 in the Server(to be connected with MII data server) with Microsoft Access DB Engine X32 bit to get the provider to read excel file(.xlsx).
  • Configure the Source System in PCo management console by following the steps below:
    1. Create New Source System.

                                                  

                   2.Select Source System Type as “OLE DB Agent” , provide a relevant name and description to it and click “OK”

                                               

                     3.Once the Source is created click on Configure and set the Data Link Properties:

• Provider Tab: Select Provider “Microsoft Office 12.0 Access Database Engine OLE DB Provider” and click on Next.

                                                              

• Next takes you to Connection , put the exact file path to be read in the Data Source field, username can be put anything for the time being as this would be overridden while configuring Agent instance.

                                              

• Make sure that the File is not open in anywhere while testing the connection and click on test connection, on successful connection you will get the following pop up.

                                              

• In the Advanced tab, check on the access permissions you would like to give the user.

                                              

• In the All Tab you will see in Data Source the exact file path you provided earlier( Kept blank here for project specific confidentiality), Extended Properties should have Value “Excel 8.0;HDR=YES” and Persist Security Info should have Value False as shown below:

                                              

                                              

• Click "OK" to save the Source as shown above.

• Select the Agent you just created , and Add an Agent Instance against that.
  • Source System should have the Source Name selected, give an appropriate Agent Name and Description, and Click OK.

                                              

• Configure the Agent Instance further by giving the Service Username and relevant Password of the server.

                                              

• Check the Legacy Type, put the Port no(9000 is default port)

                                              

• Save the Agent Instance.

• Go to services.msc and provide the system username and confirm the password again, because sometimes the PCo agent is not able to recognize credential provided initially during agent instance configuration and gives error in PCo Log. The system username and password should be same as you have put in earlier step while configuring the PCo Agent.

                                             

• In the Windows Firewall with Advanced Security, check if the port 9000 is added, if not add the respective port here in Inbound Rules.

                                             

• Configure the MII Data Server to read the excel file through the Connector. Current configuration done via OLEDB Connector as:

                                            

                                            

                       In the Settings, check the Enabled box, in the Connection tab provide the IP/Server Name of the PCo installed machine and also provide the port as configured for  in the PCo[9000 in this case].

                                           

                      On enabling check status further, it should be in running mode:

                                           

• Open MII WorkBench, Open an SQL Query, select the Data server “Test Excel” and choose “Fixed Query” Mode, and go to Query, you will see all the relevant columns available in the excel sheet per worksheets available and you can apply a simple select * from [<tablename>].

                                           

   Now you are able to access all the Excel Sheet data you needed, and you can use it further as per your requirement.

   Hope this blog will be helpful to you guys.

   Also my special Thanks to steve.stubbs for his valuable suggestions during my learning process for PCo.