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

Time:04-07

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

CodePudding user response:

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
    //...
}
  • Related