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