Web service or Sql Server procedure RRS feed

  • Question

  • User-1495538717 posted

    I have been asked to design a solution for the following problem:

    We have an ERP that is different than our corporate office ERP system, they use SAP Financials.  We are setting up a system that will transfer our financial information, A/R, AP, FA, and all journal entries to the corporate SAP Financial system.  What I need to do is extract the data from our ERP system as the entries are made into our ERP system and send them to the SAP system.  We have mapped the data from our ERP tables to the SAP table structure and are storing that mapping in seperate tables in a seperate DB.    This seperate db is also going to contain tables that we are going to use to "stage" the data that needs to be sent to the SAP system.  Corporate has given us a web service that does the transmission of the data to them to be stored in the SAP system

    What I am looking for is the best method to poplulate the "staging" tables with the data from our ERP system.  There were 2 ways I was thinking of doing this. 

    First, write a sql procedure that would run every few minutes to see if there are new transactions in our ERP system that need processing and store those transactions in the intermediate tables then the process to transfer the data to the SAP system would run to actually transmit those transactions. 

    The second method I thought of doing is when a certain transaction type was created or updated, build a web service to handle extracting the data from the current ERP system and storing the information in the "staging" tables.

    If any of you have done something similar to this or have any feedback to pro's/con's of doing it one way or the other, I would like to here from you.  I am not that experienced with building web services so I am more inclined to using purely a SQL solution.

    Thank you for any advise/experienes you can share with me.



    Monday, July 16, 2012 9:03 AM

All replies

  • User-738028663 posted

    Your first method will be much better if you have more processing to do in sp , as it is much efficient and faster and more reliable because you are processing or initiating the transactions at db end only , will not prefer web service as it is a remote way , so will be little slow , but i the reason i will not recommend to go with web service when you processing of transactions are much/alot.

    Monday, July 16, 2012 1:38 PM
  • User37438451 posted

    If possible, put the "stage" (I call it integration tables) on the same SQL-server. Then it will be much easier and faster to create an SQL-job that looks for changes and transfers the data to the integration tables.

    I don’t see any reason why you should use web services in this scenario. Maybe if SQL-connections are not allowed between the ERP-database and the integration-database, but that is most unlikely.

    Monday, July 16, 2012 4:30 PM
  • User240584894 posted

    Data transformations can be handled in  much better way using Sql Server Integrated Services SSIS ?

    Monday, July 16, 2012 4:42 PM
  • User-1404016747 posted

    I would use a stored procedure if the application has direct access to the database.
    If the application is a remote application and you do not want to allow direct access to the database then use a web service.

    As stated before the stored procedure would be faster.

    Monday, July 16, 2012 4:44 PM
  • User-1495538717 posted

    Thank you to all of you for your feedback.  It was very helpful. 

    Tuesday, July 17, 2012 10:36 AM
  • User-738028663 posted

    Always close the thread by marking answer , which reflects the right status your problem is solved.

    Wednesday, July 18, 2012 1:05 PM
  • User37438451 posted
    I think it is more correct to mark one of the answers, not your question.
    Thursday, July 26, 2012 5:11 PM