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.
