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 :)

this might help stackoverflow.com/a/55229423/10376537

Tim Mylott
– Tim Mylott

2021-02-05 14:35:07 +00:00
Commented Feb 5, 2021 at 14:35 — Tim Mylott
– Tim Mylott, Commented Feb 5, 2021 at 14:35

Tyron78 · Accepted Answer · 2021-02-05 14:15:17Z

0

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);
}

answered Feb 5, 2021 at 14:15

Tyron78

4,1973 gold badges21 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aritra Sarkar Over a year ago

Hi @Tyron78, I've made the necessary changes in the code, but not being able to run the code. Install Newtonsoft.Json from Manage NuGet Packages and added using Newtonsoft.Json; in the Namespaces. The build is successfull, but, If I run the package, the script component fails with the following error, "Could not load file or assembly 'Newtonsoft.Json, Version=12.0.0.0, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference".

Collectives™ on Stack Overflow

Get data from an API into SSIS Script Component Output Buffer

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related