So you are trying to retrieve 'List data' from "Sharepoint
Online", is that correct ?
I went through this exercise recently, it was a pain to get it working, mainly because of Microsoft's Authentication procedure which is not well documented.
You need to read this:
https://allthatjs.com/2012/03/28/remote ... nt-online/
and to get an understanding of how it works, try this step by step procedure with a REST client
(eg. SOAP UI, Postman, Fiddler etc.)
http://paulryan.com.au/2014/spo-remote- ... tion-rest/
- But to summarize, every-time you read from Sharepoint Online, you need to provide, with each WebService or GET method requests an authentication "Cookie" header and a "User-Agent" header.
- If you write to SharePoint Online (eg. POST method), you'll need to provide an extra header called 'X-RequestDigest'
(haven't tried it..)
But then the Question is, how do I get this 'Cookie' thing from DataStage ?
I just used this Java code:
https://gist.github.com/mirontoli/3702971
Edit the code with your Microsoft account logins, get a java compiler
(it worked like a charm with Eclipse), run it, and in the output you'll find something like this:
Code: Select all
Set-Cookie: rtFa=0U1zw+TnLmLfDtzmppbuJgD
Set-Cookie: FedAuth=77u/PD94bWfdfW path=/secure; HttpOnly
You can then create a unique Cookie header and User-Agent header that you'll need to pass with each request:
Code: Select all
Cookie: rtFa=0U1zw+TnLmLfDtzmppbuJgD; FedAuth=77u/PD94bWfdfW path=/secure; HttpOnly
User-Agent: Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Win64; x64; Trident/5.0)
I have tried those 2 approaches in DataStage:
1)WebService Client Stage
The WSDL you were looking for is here:
https://<yoursite>.sharepoint.com/_vti_bin/Lists.asmx?WSDL
But as usual, it is not well formatted, and DataStage will complain that you have duplicate 'namespaces':
I removed the first one and then was able to have all the SOAP 1.1/1.2 Methods recognized by DataStage.
You then need to create, in the WebService Client Stage an input column that contains the SOAP header with 'Cookie' and 'User-Agent' (this has to be structured in XML format
)
Read this to see how it is done:
https://www.ibm.com/developerworks/webs ... index.html
But that is just FYI... I never got this approach to work
... Authentification fails with
'403 Forbidden' error message) ... I guess my XML syntax was incorrect.. But you might be more lucky, and if you are, what's great with this approach is that you'll have all the necessary output columns ready to be used.
2) REST approach with Hierarchical Stage - It worked like a charm !
(available from DataStage 11.3 only! formerly known as XML Stage in 9.1, but you don't have the REST feature in 9.1! ... So not an option for you, until you upgrade to newer DS version... Support for 9.1 expires next year btw )
- Used the GET method in the Hierarchical Stage
- Easy to add the 'Cookie' and 'User-Agent' header
BUT, with this approach you'll get the response in HTML or XML format... You'll need to find out how to retrieve the data you're after from all the mess..
(Haven't done it yet, but I'm sure it's do-able and maybe the reponse structure can be found somewhere on the Microsoft website)
But if your workaround works fine, maybe there's no much value in going through all this trouble again...