question

IgnacioCamposMoyano-0772 avatar image
0 Votes"
IgnacioCamposMoyano-0772 asked cooldadtx answered

Connection SSIS to Sharepoint using Oauth

Hi,

I would like to connect to Sharepoint with SSIS, but without tools from third suppliers. Is there any aplications from Microsoft that allow us to connect both tools?

If the answer is true, would you told me in which version of SSIS it runs?

IMPORTANT: for that, I need to connect by Oauth 2.0

Thanks you in advance!

sql-server-integration-servicesoffice-sharepoint-server-customization
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

cooldadtx avatar image
0 Votes"
cooldadtx answered

Oauth is an authentication protocol and is not relevant for having SSIS talk to SharePoint (SP). .NET ships with both HttpClient and WebClient to talk to HTTP services like SharePoint. SSIS supports talking to HTTP services directly so you don't need anything special but it is probably easier to do it in a script task. However you can configure the connection itself using the standard SSIS HTTP Connection Manager. Refer to the SSIS documentation on how to do this.

Ideally you should simply use the existing pre-built libraries to talk to SP as it would be dramatically easier. However if for some reason you enjoy doing things the hard way you can manually build each HTTP request yourself. To do that you'll need to be very comfortable with the SP API (note that what version you use completely depends upon the version of SP you are trying to talk to).

You probably would do better to start with a simple console app or Postman to get the SP API calls working correctly (including OAuth), then have Postman convert it to C# code for you. If you are comfortable with C# and either of the clients then you can do it by hand as well. Once you have the API calls working outside SSIS you can put the logic into an SSIS Script task, hook up the HTTP connection you preconfigured and you should be good. All this is documented in SSIS.

For OAuth itself I'm assuming you're using client credentials. If you don't know how OAuth works then you need to read up on it as well as it is too large a topic for this forum. Ultimately, you'll make an API call to the OAuth endpoint with your client ID/secret/scope settings (from the SP API docs) to get a bearer token. You should do this once in your SSIS package. Then all subsequent calls to the SP REST API would include the bearer token as part of the Authorization header. There are plenty of examples online on how to do this but here's a starter post.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.