Forum Discussion
Connecting to MS Office 365 Graph API through on-premise SSIS
Need help connecting to MS Office 365 Graph API through on-premise SSIS. Standard SSIS components don't work due to authentication (need OAuth 2.0)
I do have a code from Power BI (M Query) that works, so most probably need an equivalent in C#.
" = () as text => let Source = Json.Document(Web.Contents("https://login.microsoftonline.com/" & #"Tenant Id" & "/oauth2/token", [Content = Text.ToBinary("client_id=" & #"Client Id" & "&resource=https%3A%2F%2Fgraph.microsoft.com" & "&client_secret=" & #"Client Secret" & "&grant_type=client_credentials") ,Headers=[#"Content-Type"="application/x-www-form-urlencoded", Accept="application/json"]])), access_token = Source[access_token] in access_token " #"Tenant Id", #"Client Id", #"Client Secret" are parameters that I am planning to pass.
What and how should be used in SSIS?
Thank you,
Dovilė
There is no inbuilt connector to call OAuth API in SSIS (e.g. Graph API using OAuth). However if you are open for trying 3rd party connectors then check out ZappySys JSON Source and OAuth Connection. Basically on OAuth Connection just set like below
1) Version=OAuth2
2) Grant Type=Client Credentials
3) Token URL=https://login.microsoftonline.com/<Tenant Id>/oauth2/token
4) On Advanced Tab Enter extra attributes for Token URL as below
resource=https://graph.microsoft.com
5) Save connection
Thats it ... after that you can use OAuth Connection in components like JSON Source, Web API Destination and so on... if you use JSON Source set Format as OData so pagination is taken care too.
For using 3-legged OAuth you can try something like below
https://zappysys.com/blog/calling-office-365-api-using-ssis-graph-api-mail-calendar-contacts-onedrive-excel/Calling Microsoft GraphAPI in SSIS using ZappySys JSON Source + OAuth Connection
