c# - Get data from an API into SSIS Script Component Output Buffer

Question

I'm new in SSIS and I'm not very familiar with C#.

I was trying to achieve the following, as a similar work is in the pipeline in the next couple of weeks.

I have an online API https://jsonplaceholder.typicode.com/todos. It is having values in the following format.

{
    "userId": 1,
    "id": 1,
    "title": "delectus aut autem",
    "completed": false
}

I'm using a script component, added Output columns (all DT_STR), userId, Id, title, completed, I'm able to get the response from the API and store the response in a variable result. Please find the piece of code below.

public override void CreateNewOutputRows()
{
   string url = String.Format("https://jsonplaceholder.typicode.com/todos");
   WebRequest requestObject = WebRequest.Create(url);

   requestObject.UserDefaultCredentials = true;
   requestObject.PreAuthenticate = true;
   requestObject.Credentials = CredentialCache.DefaultNetworkCredentials;

   requestObject.Method = "GET";
   HttpWebResponse requestObject = null;
   responseObject = (HttpWebResponse)requestObject.GetResponse();

   string result = null;
   using (Stream stream = responseObject.GetResponseStream())
   {
      StreamReader sr = new StreamReader(stream);
      result = sr.ReadToEnd();
      sr.Close();
   }
}

My aim is to store the result in the Script Component Output columns, using OutputBuffer.AddRow(), so that I can map them to an Oledb Destination.

Any help will be highly appreciated. Thanks in advance :)

question from:https://stackoverflow.com/questions/66059193/get-data-from-an-api-into-ssis-script-component-output-buffer

Answer 1

In C#, define a class such as the following:

public class Value
{
    public int userId { get; set; }
    public int id { get; set; }
    public string title { get; set; }
    public Boolean completed { get; set; }
}

Then - after populating the result string with the response of your request - process the result string via JsonConvert:

List<Value> valueList = JsonConvert.DeserializeObject<List<Value>>(response);

This done, you can loop through the list and push every value to its corresponding output. Following an example with Console.WritLine:

foreach (Value v in valueList)
{
    Console.WriteLine(v.title);
}