locked
Post to a web service using ssis RRS feed

  • Question


  • I have a table is SQL server database A that is my source.

    I have another database B which is accessed via webservice call.(its a CRM server basically).

    My intention is to transfer data from A to B while B is accessible only via web service. I need to Trasnfer the data from the source database A to the destination Database B by calling the webservice.

    Is there a way where I can retrieve whole set of rows in source table in preexecute(), And transfer the data to Database destination B by calling the webservice?

    Please Help me guys?

    Wednesday, September 2, 2015 10:51 AM

Answers

  • Correct.

    But you should take a look at your web service first. Maybe it has a kind of bulk mode which accepts XML or JSON. Then you can create a XML or JSON and you need to call it only once.

    Wednesday, September 2, 2015 2:06 PM

All replies

  • Sure. Use the Web Service Task.
    Wednesday, September 2, 2015 11:01 AM
  • Cheers for the reply Stefan , yes i tried that to get the data from webservice to sql , but i am not really sure how to do from SQL TABLE to the webservice ...Any example i can look in to it ..? Can you share with me if you have anything please.

    Wednesday, September 2, 2015 12:59 PM
  • You need to feed the Web Service input.

    The problem is imho what does the Web Service accept as parameters and what methods does it have?

    The only thing I've did is to retrieve a result set and loop over it and call the service in the loop body.

    Wednesday, September 2, 2015 1:40 PM
  • Oh.i see i understand what you mean instead of the get service ..i need to use the post service method on my webservice to post it and Loop it using the Foreach Loop?
    Wednesday, September 2, 2015 1:54 PM
  • Correct.

    But you should take a look at your web service first. Maybe it has a kind of bulk mode which accepts XML or JSON. Then you can create a XML or JSON and you need to call it only once.

    Wednesday, September 2, 2015 2:06 PM
  • Ok ..I will have a look ..Thank you very much for your Help Stefan..i really appreciate.

    Would you mind if i comeback again if i have any question later because you are been really helpful.

    Wednesday, September 2, 2015 2:11 PM
  • Hi ,

    I selected the webservice post mentod but when trying to select thw web method documentation shows me error " The selected Web Method contains unsupported arguments"

    Can you help me with the pls.

    Thanks 


    Jay

    Thursday, September 24, 2015 11:15 AM
  • Nope, not without knowing what web service it is..

    Thursday, September 24, 2015 12:34 PM
  • What is the CRM server software in use?

    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

    Friday, September 25, 2015 12:32 PM
  • Right now i am using the test system basically which is not used by others still get the same msg , i also tried with the live system which s/w is in use and get the same msg ..

    Jay

    Friday, September 25, 2015 12:47 PM
  • Do you got anyother way of doing it ...Please share if you got any ideas of calling the webservice which got only post method ...So that i can transfer my data from sql server to webservice destination

    Jay

    Friday, September 25, 2015 1:33 PM
  • Do you got anyother way of doing it ...Please share if you got any ideas of calling the webservice which got only post method ...So that i can transfer my data from sql server to webservice destination

    Jay

    What is the CRM server software in use?

    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

    Friday, September 25, 2015 2:04 PM
  • Yes its is in use

    Jay

    Friday, September 25, 2015 2:08 PM
  • Yes its is in use

    Jay

    What is the CRM Server software manufacturer ? Who is producing the CRM software? What is the CRM software name?

    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

    Friday, September 25, 2015 2:12 PM
  • Sales force

    Jay

    Friday, September 25, 2015 2:17 PM
  • Sales force

    Jay

    Jay,

    You cannot use the standard Web Service Task to work with Salesforce. The standard Web Service Task is useless 99% of the time.

    I would recommend you check the commercial COZYROC SSIS+ library. It includes Salesforce adapters which allows you to work with the Salesforce service. The adapters are very easy to use. No programming skills are required.


    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

    Friday, September 25, 2015 2:19 PM
  • I understand but i got only the created webservice for post the data to the destinbation from sql server source..i dont have anyother options to access directly the destination database other than via the created webservice which post to the destination


    Jay

    Friday, September 25, 2015 2:27 PM
  • I understand but i got only the created webservice for post the data to the destinbation from sql server source..i dont have anyother options to access directly the destination database other than via the created webservice which post to the destination


    Jay

    Jay,

    That's why you need an adapter. Check COZYROC SSIS+ library. It is based on the Salesforce web services.


    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

    Friday, September 25, 2015 2:42 PM