January 5, 2017 at 5:54 pm
I am working on an ETL process which pulls from several APIs. Essentially, I capture the JSON output and then leverage OPENJSON to parse the output. I have been able to pull together some code in a script component of SSIS to handle Basic Authentication when calling the API; However, OAuth 1 and OAuth 2 Authentication has proven to be a bit confounding. Specifically, I am attempting to connect to Quickbooks Online, Google Directory API, and Google Sheets.
I am trying ZappySys components, but I have not been able to successfully get OAuth to work. I am encountering access denied/forbidden/insufficient permission errors. I have performed key setup on Quickbook and Google side of the fence. I know that I am overlooking something - or not quite understanding all things needed for a successful connection. Has anyone successfully processed an API using OAuth in SSIS (2016) for the specific sources I noted? If so, please enlighten me... or direct me to some documented enlightenment. I appreciate any help extended.
Just to clarify, I am not looking for a solution to do any parsing - just simply connect and retrieve the JSON string. Thank you in advance.
January 5, 2017 at 6:14 pm
John Magnabosco (1/5/2017)
I am working on an ETL process which pulls from several APIs. Essentially, I capture the JSON output and then leverage OPENJSON to parse the output. I have been able to pull together some code in a script component of SSIS to handle Basic Authentication when calling the API; However, OAuth 1 and OAuth 2 Authentication has proven to be a bit confounding. Specifically, I am attempting to connect to Quickbooks Online, Google Directory API, and Google Sheets.I am trying ZappySys components, but I have not been able to successfully get OAuth to work. I am encountering access denied/forbidden/insufficient permission errors. I have performed key setup on Quickbook and Google side of the fence. I know that I am overlooking something - or not quite understanding all things needed for a successful connection. Has anyone successfully processed an API using OAuth in SSIS (2016) for the specific sources I noted? If so, please enlighten me... or direct me to some documented enlightenment. I appreciate any help extended.
Just to clarify, I am not looking for a solution to do any parsing - just simply connect and retrieve the JSON string. Thank you in advance.
I have used OAuth in a Script Component source, but it was to retrieve Facebook analytics data.
Have you got an access token?
Are you able to successfully submit a URL + access token from a browser & get a response, or do you have to use Google's API?
I managed to do the whole thing in native C#, apart from the JSON shredding, for which I used Newtonsoft.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 5, 2017 at 6:32 pm
Yes, I was able to gain an access key. I was able to get a JSON string from Google Sheets and Directory API through Google's API Explorer - but not outside of this interface. I was able to get a JSON string of sample data (not the real stuff) from Quickbook's API Explorer. Similar to Google, I haven't been able to achieve a JSON string outside of this interface.
I am more than willing to write a script component in SSIS (2016) to achieve an API pull; However, C# is not my primary (or secondary) language. If you have any examples, or URLs to sites with examples I would greatly appreciate it. I am not asking for you (or anyone) to do the hard work. I am simply seeking some guidance and examples. Majority of the examples that I have found online leverage references of third party SDKs. If this can be achieved with references that are native to SSIS -- bonus!
Thanks!
January 8, 2017 at 9:43 am
OK, here is some code which may help you. It won't run as it stands, but it might help you understand how I got data back from Facebook using OAuth.
Firstly, here's my USING block:
#region Namespaces
using System;
using System.Linq;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Newtonsoft.Json;
using System.Collections.Generic;
using System.Net.Http;
using System.Net;
using System.IO;
using System.Globalization;
#endregion
Here's an edited-down version of my CreateNewOutputRows() routine.
public override void CreateNewOutputRows()
{
string resp;
DateTime ranAt = DateTime.Now;
string retrieveFields = "account_id,campaign_id,adset_id, ad_id, campaign_name, adset_name, ad_name, date_start,spend,total_actions,total_action_value,total_unique_actions,reach,frequency,impressions,inline_link_clicks,unique_clicks,actions";
var keyValues = new List<KeyValuePair<string, string>>();
string baseUri = String.Concat("https://graph.facebook.com/v2.5/act_", Variables.accountnumber, "/insights?");
string Uri;
string date;
string targetTable = Variables.TargetTable;
//The value of Breakdowns depends on the target table
string breakdowns;
if (targetTable == "CampaignPerformanceAgeGender")
breakdowns = "age,gender";
else
breakdowns = "impression_device,placement";
HttpWebRequest request;
HttpWebResponse response;
DateTime startDate;
DateTime.TryParseExact(Variables.datestart, "yyyy-MM-dd", new CultureInfo("en-GB"), DateTimeStyles.None, out startDate);
DateTime endDate;
DateTime.TryParseExact(Variables.dateend, "yyyy-MM-dd", new CultureInfo("en-GB"), DateTimeStyles.None, out endDate);
//Process between start and end date one day at a time, to avoid Facebook data limits (500 Internal Server Error etc)
foreach (DateTime day in EachDay(startDate, endDate))
{
resp = "";
#region //Keyvalues
keyValues.Clear();
keyValues.Add(new KeyValuePair<string, string>("breakdowns", breakdowns));
keyValues.Add(new KeyValuePair<string, string>("access_token", Variables.accesstoken));
date = day.ToString("yyyy-MM-dd");
keyValues.Add(new KeyValuePair<string, string>("time_range", String.Concat("{'since': '", date, "', 'until': '", date, "'}")));
#endregion
//Build the URI to be submitted from all of the components
Uri = string.Concat(baseUri, string.Join("&", keyValues.Select(x => x.Key + "=" + x.Value).ToArray()));
//Submit the URI and decode the response (hopefully a JSON object containing the data we're interested in)
request = (HttpWebRequest)WebRequest.Create(Uri);
response = (HttpWebResponse)request.GetResponse();
using (Stream stream = response.GetResponseStream())
{
StreamReader reader = new StreamReader(stream);
Char[] readBuffer = new Char[256];
int count = reader.Read(readBuffer, 0, 256);
//Stream the response to a string
while (count > 0)
{
String output = new String(readBuffer, 0, count);
resp += output;
count = reader.Read(readBuffer, 0, 256);
}
if (targetTable == "xxx")
{
# region //When streaming is complete, shred the JSON and send to the various output columns, to be fired at the database.
foreach (ReportResult r in JsonParseReportData(resp))
{
//Shredding and .AddRow() here
}
#endregion
}
else //Assume it's yyyy
{
# region //When streaming is complete, shred the JSON and send to the various output columns, to be fired at the database.
foreach (ReportResult r in JsonParseReportData(resp))
{
//Shredding and .AddRow() here
}
#endregion
}
}
}
}
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 8, 2017 at 3:39 pm
Phil,
Thank you for the guidance. I will give it a try and let you know how it goes. Thank you for your time and consideration.
February 28, 2022 at 9:55 am
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.
Greeting,
Rachel Gomez
February 28, 2022 at 10:08 am
Oauth is an authentication protocol and is not relevant for having SSIS talk to SharePoint (SP).
--
Greeting,
Rachel Gomez
SharePoint is not mentioned anywhere else in this thread, so why mention it here, on a thread which is more than five years old?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply