SQL Server Call/ SSIS to REST API Web Service (Workday) - (Not Weather API)

Question

SQL Server Call/ SSIS to REST API Web Service (Workday) - (Not Weather API)

venkyzrocks

SSCommitted

Points: 1907
More actions
September 26, 2016 at 3:05 pm

#330494

Hi All,
I'm looking to call a web service (REST API/SOAP) doesn't matter through SSIS/Stored procedure. SSIS does have a web service call but it only accepts simple WSDL files and cannot read other methods.
I tried this through stored procedure and get other errors. Still working to get my SOAP message right I guess.
http://www.vishalseth.com/post/2009/12/22/Call-a-webservice-from-TSQL-(Stored-Procedure)-using-MSXML.aspx
ZaapSys does have a Powerpack utility and it works but it's paid.
I can build the Integration from the workday side but then it's a 2 way step like dump a csv, run a listener and grab the file.
Does anyone have a better approach ?
Thanks,
V

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Smendle Hall of Fame Points: 3969 More actions · Answer 1

venkyzrocks (9/26/2016)
Hi All,
I'm looking to call a web service (REST API/SOAP) doesn't matter through SSIS/Stored procedure. SSIS does have a web service call but it only accepts simple WSDL files and cannot read other methods.
I tried this through stored procedure and get other errors. Still working to get my SOAP message right I guess.
http://www.vishalseth.com/post/2009/12/22/Call-a-webservice-from-TSQL-(Stored-Procedure)-using-MSXML.aspx
ZaapSys does have a Powerpack utility and it works but it's paid.
I can build the Integration from the workday side but then it's a 2 way step like dump a csv, run a listener and grab the file.
Does anyone have a better approach ?
Thanks,
V

Can you not just use a script task and import the modules you need to make this call?

venkyzrocks SSCommitted Points: 1907 More actions · Answer 2

Well Yes you could, seeing if someone has script that I could reuse. I'm not proficient in C# or VB.Net.

Half the scripts online don't work or do a good job of how to set it up.

Thanks,

V

phy6nando SSC Journeyman Points: 79 More actions · Answer 3

Has anyone been able to find a solution to this?

It has been some time since I've coded in SQL but can't think it would be that difficult.

I actually developed the WD Studio integration and can have it run every fifteen minutes (there's also a workaround to run more frequently by doing a time delay but that is a massively tedious job)...

Ideally, I'd like to run a script from SQL to make the call and dump the files we have generated...I do have the xsd and xslt files that translate the data.

corey lawson Hall of Fame Points: 3773 More actions · Answer 4

CozyRoc may have Workday-specific source and destination component by now.

They certainly have their own web service source and destination connectors (SalesForce, etc), as well as generic ones.

I have used the SalesForce components (as well as a couple of Script+ scripts for Salesforce) to great ETL effect in the past.

CozyRoc's components are free to develop with, at least.

venkyzrocks SSCommitted Points: 1907 More actions · Answer 5

Thanks, but we were able to accomplish this via Zappysys. Their component wasn't that expensive and works well for our needs. WD still recommends the SFTP download and upload method.

corey lawson Hall of Fame Points: 3773 More actions · Answer 6

excellent! where I work recently migrated to WD. Right now we do our data exchanges via XML files. which is ok but... just today a co-worker asked about budget codes. Wish we had a web api based way to do some of this with WD.

Zappysys I think explains enough for what needs to be done with SOAP/WSDL/REST...