Insert the response from the API in to SQL server table through SSIS script task

Question

I am trying to create a SSIS package that makes a call to the REST API and insert the responses into the Azure SQL server table. For this I am using the script task and ADO.NET connection manager. The API response is in the JSON format like below

[{"id":1,"name":"AX BD","description":"D","shippingFreeze":false,"receivingFreeze":false,"mouseNorovirus":false,"irradiatedFeed":true,"createdAt":"2022-02-24T10:03:50.09","lastUpdated":"2022-02-24T10:03:50.09"},
 {"id":2,"name":"AX PD","description":"B","shippingFreeze":false,"receivingFreeze":false,"mouseNorovirus":false,"irradiatedFeed":false,"createdAt":"2022-02-24T10:03:50.09","lastUpdated":"2022-02-24T10:03:50.09"}]

and below in the script task I tried to make call to the REST API and converted the response into JSON, but I am not sure how to iterate through each of the JSON record and insert them mapping the values from the JSON in to the sqlCmd.Parameters.AddWithValue

   public void Main()
    {
        try
        {
            string serviceUrl = Dts.Variables["$Project::ServiceUrl"].Value.ToString();
            HttpClient client = new HttpClient();
            client.BaseAddress = new Uri(serviceUrl);
            client.DefaultRequestHeaders.Accept.Add(
                new MediaTypeWithQualityHeaderValue("application/json"));
            string APIUrl = string.Format(serviceUrl + "/rooms");
            var response = client.GetAsync(APIUrl).Result;
            if (response.IsSuccessStatusCode)
            {
                var result = response.Content.ReadAsStringAsync().Result;
                dynamic res_JSON = JsonConvert.DeserializeObject(result);

                ConnectionManager cm = Dts.Connections["SurplusMouse_ADONET"];
                var sqlConn = (System.Data.SqlClient.SqlConnection)cm.AcquireConnection(Dts.Transaction);

                using (var sqlCmd = new System.Data.SqlClient.SqlCommand(
                "INSERT INTO [dbo].[RM_Room]([ROOMID],[NAME],[DESCRIPTION],[SHIPPING_FREEZE],[RECEIVING_FREEZE],[MOUSE_NOROVIRUS],[IRRADIATED_FEED])" +
                        "VALUES(@ROOMID,@NAME,@DESCRIPTION,@SHIPPING_FREEZE,@RECEIVING_FREEZE,@MOUSE_NOROVIRUS,@IRRADIATED_FEED,)", sqlConn))
                { 
                      sqlCmd.CommandType = CommandType.Text;
                      sqlCmd.Parameters.AddWithValue("@ROOMID", xxxxx);
                      .................. 


                    int rowsAffected = sqlCmd.ExecuteNonQuery();
                }
                cm.ReleaseConnection(sqlConn);
           }
        }
        catch (Exception ex)
        {
            Dts.TaskResult = (int)ScriptResults.Failure;
        }
    }

This is my first project using the SSIS scripting and the call to the API, any help is greatly appreciated

Answer 1

You can iterate res_JSON as below snippet sample code:

dynamic res_JSON = JsonConvert.DeserializeObject(result);
foreach (var jsonObj in res_JSON)
{
    //Clear Parameter of sqlCmd
    sqlCmd.CommandType = CommandType.Text;
    sqlCmd.Parameters.AddWithValue("@ROOMID", jsonObj.id);
    sqlCmd.Parameters.AddWithValue("@NAME", jsonObj.name);
    sqlCmd.Parameters.AddWithValue("@DESCRIPTION", jsonObj.description);
    //You can access another props of jsonObj also
    int rowsAffected = sqlCmd.ExecuteNonQuery();
}

or for readability this is better to have equivalent class of you json like:

public class JsonObj
{
    public int id { get; set; }
    public string name { get; set; }
    public string description { get; set; }
    public bool shippingFreeze { get; set; }
    public bool receivingFreeze { get; set; }
    public bool mouseNorovirus { get; set; }
    public bool irradiatedFeed { get; set; }
    public DateTime createdAt { get; set; }
    public DateTime lastUpdated { get; set; }
}

and deserialize the result as array of JsonObj like:

var res_JSON = JsonConvert.DeserializeObject<JsonObj[]>(result);
foreach (var obj in res_JSON)
{
    //obj.id
    //obj.name
    //...
}