Calling API from SSIS and storing the JSON result into the table (Using Script Task)

In this post, we will see how we will call the API and extracting the data from API and load it into the table using Script task.

In the previous post, we seen that Calling API from SQL Server and storing the JSON result into the table

Read here:  Calling API from SQL Server and storing theJSON result into the table

https://bageshkumarbagi-msbi.blogspot.com/2021/06/calling-api-from-sql-server-and-storing.html

We are taking Script task. 

Click ok the Edit Script. Here we are writing the code in C#. We are including the below name spaces.

using System; using System.Data; using Microsoft.SqlServer.Dts.Runtime; using System.Windows.Forms; using System.Net; using System.Net.Http; using System.Net.Http.Headers; using System.IO; using System.Collections.Generic; using System.Text; using System.Web.Script.Serialization; using System.Data.SqlClient;

Highlighted namespace we need  to add from the reference.

Below is the code to calling the API and extracting the JSON data from it and parsing it and finally we are loading it into the database table.

For testing we are using the Below API which is available for online for the free testing.

https://jsonplaceholder.typicode.com/posts

Below is the JSON data. 

Now we are creating property class

public class lPosts         {               public List<posts> data { get; set; }         }           public class posts         {             public string userid { get; set; }             public string id { get; set; }             public string Post_title { get; set; }             public string Post_body { get; set; }         }

 

In main we are writing the below code.

public void Main()         {                   try             {                 string connetionString = null;                 string sql = null;                 string serviceUrl = "https://jsonplaceholder.typicode.com/posts";                 HttpClient client = new HttpClient();                 client.BaseAddress = new Uri(serviceUrl);                 var serializer = new JavaScriptSerializer();                 // Add an Accept header for JSON format.                    client.DefaultRequestHeaders.Accept.Add(                   new MediaTypeWithQualityHeaderValue("application/json"));                 connetionString = "Data Source=BAGESH\\BAGESHDB;Initial Catalog=TestDB; Trusted_Connection=True;";                   string APIUrl = string.Format(serviceUrl);                 var response = client.GetAsync(APIUrl).Result;                 if (response.IsSuccessStatusCode)                 {                     var result = response.Content.ReadAsStringAsync().Result;                       var dt = serializer.Deserialize<Dictionary<string,                       string>[]>(result);                     using (SqlConnection cnn = new SqlConnection(connetionString))                     {                         cnn.Open();                         sql = "insert into Posts_details (userID, Id,Post_title,Post_body) values(@userID, @Id,@Post_title,@Post_body)";                         foreach (var item in dt)                        {                                                      using (SqlCommand cmd = new SqlCommand(sql, cnn))                             {                                   cmd.Parameters.Add("@userID", SqlDbType.NVarChar).Value = item["userId"].ToString();    cmd.Parameters.Add("@Id", SqlDbType.NVarChar).Value = item["id"].ToString();     cmd.Parameters.Add("@Post_title", SqlDbType.NVarChar).Value = item["title"].ToString();   cmd.Parameters.Add("@Post_body", SqlDbType.NVarChar).Value = item["body"].ToString();                                    int rowsAdded = cmd.ExecuteNonQuery();                             } }                         cnn.Close();                     }                     Dts.TaskResult = (int)ScriptResults.Success;                 } }             catch (Exception ex)             {                 Dts.Events.FireError(0, "Fire Error", "An error occurred: " + ex.Message.ToString(), "", 0);                 Dts.TaskResult = (int)ScriptResults.Failure;             }         }

 

Compile the code and build it save and close it.

Here we are not using Newtonsoft.Json

Now our package is ready to run.

Before running the package see the records in the table.

  

Running the package now.  

Package executed successfully.

See the records in the table.

  

Data loaded successfully.