Unable to insert data from the JSON in to the SQL server table

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",    "description": "B",    "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": "PD ",    "description": "F",    "shippingFreeze": false,    "receivingFreeze": false,    "mouseNorovirus": false,    "irradiatedFeed": false,    "createdAt": "2022-02-24T10:03:50.09",    "lastUpdated": "2022-02-24T10:03:50.09"  }
]}

Table in the SQL server

I am trying to iterate through the JSON and inserting each of them n to the SQL server tables like below

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))
  {
     foreach (var jsonObj in res_JSON)
      {
         sqlCmd.CommandType = CommandType.Text;
         sqlCmd.Parameters.AddWithValue("@ROOMID", jsonObj.id.ToString());              
         sqlCmd.Parameters.AddWithValue("@NAME", jsonObj.name.ToString());
         sqlCmd.Parameters.AddWithValue("@DESCRIPTION", jsonObj.description.ToString());
         sqlCmd.Parameters.AddWithValue("@SHIPPING_FREEZE", (jsonObj.shippingFreeze.ToString() == "true") ? "T" : "F");
         sqlCmd.Parameters.AddWithValue("@RECEIVING_FREEZE", (jsonObj.receivingFreeze.ToString() == "true") ? "T" : "F");
         sqlCmd.Parameters.AddWithValue("@MOUSE_NOROVIRUS", (jsonObj.mouseNorovirus.ToString() == "true") ? "T" : "F");
         sqlCmd.Parameters.AddWithValue("@IRRADIATED_FEED", (jsonObj.irradiatedFeed.ToString() == "true") ? "T" : "F");
         int no_exec = sqlCmd.ExecuteNonQuery();
     }
   }
   cm.ReleaseConnection(sqlConn);
  }}
  catch (Exception ex)
  {
    Dts.TaskResult = (int)ScriptResults.Failure;
  }

When I debug it is throwing error like

And the Stack trace

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at ST_b0ab9fccfaa640008ecd1bdf57ec4324.ScriptMain.Main() in C:\Users\dv_admin\AppData\Local\Temp\2\vsta\43ff553a1bba27\ScriptMain.cs:line 76

I am not sure what is that I am missing here. Any help is greatly appreciated

Updated code

   public async void Main()
    {
        try
        {
            var sqlConn = new System.Data.SqlClient.SqlConnection();
            ConnectionManager cm = Dts.Connections["SurplusMouse_ADONET"];

            string serviceUrl = Dts.Variables["$Project::ServiceUrl"].Value.ToString();
            ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12 | SecurityProtocolType.Tls11 | SecurityProtocolType.Tls;
            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 = await response.Content.ReadAsStringAsync();

                try
                {
                    sqlConn = (System.Data.SqlClient.SqlConnection)cm.AcquireConnection(Dts.Transaction);
                    const string query = @"INSERT INTO dbo.RM_Room
                                      (ROOMID, NAME, DESCRIPTION, SHIPPING_FREEZE, RECEIVING_FREEZE, MOUSE_NOROVIRUS, IRRADIATED_FEED)
                                       SELECT id, name, description,
                                              CASE shippingFreeze WHEN 1 THEN 'T' ELSE 'F' END,
                                              CASE receivingFreeze WHEN 1 THEN 'T' ELSE 'F' END,
                                              CASE mouseNorovirus WHEN 1 THEN 'T' ELSE 'F' END,
                                              CASE irradiatedFeed WHEN 1 THEN 'T' ELSE 'F' END
                                       FROM OPENJSON(@json)
                                       WITH (
                                             id int,
                                             name varchar(100),
                                             description varchar(1000),
                                             shippingFreeze bit,
                                             receivingFreeze bit,
                                             mouseNorovirus bit,
                                             irradiatedFeed bit
                                             ) j;";
                    using (var sqlCmd = new System.Data.SqlClient.SqlCommand(query, sqlConn))
                    {
                        sqlCmd.Parameters.Add("@json", SqlDbType.NVarChar, -1).Value = result;
                        await sqlConn.OpenAsync();
                        await sqlCmd.ExecuteNonQueryAsync();
                    }
                }
                catch (Exception ex)
                {
                    Dts.TaskResult = (int)ScriptResults.Failure;
                }
                finally
                {
                    if (sqlConn != null)
                        cm.ReleaseConnection(sqlConn);
                }

            }
        }
        catch (Exception ex)
        {
            Dts.TaskResult = (int)ScriptResults.Failure;
        }
    }

Answer 1

Your primary issues is an extra comma in the SQL, which is a syntax error.

It's probably easier to just pass the whole JSON to SQL Server and shred it using OPENJSON

var result = await response.Content.ReadAsStringAsync();

ConnectionManager cm = Dts.Connections["SurplusMouse_ADONET"];

SqlConnection sqlConn = null;
try
{
    sqlConn = (SqlConnection)cm.AcquireConnection(Dts.Transaction));
    const string query = @"
INSERT INTO dbo.RM_Room
  (ROOMID, NAME, DESCRIPTION, SHIPPING_FREEZE, RECEIVING_FREEZE, MOUSE_NOROVIRUS, IRRADIATED_FEED)
SELECT
  id,
  name,
  description,
  CASE shippingFreeze WHEN 1 THEN 'T' ELSE 'F' END,
  CASE receivingFreeze WHEN 1 THEN 'T' ELSE 'F' END,
  CASE mouseNorovirus WHEN 1 THEN 'T' ELSE 'F' END,
  CASE irradiatedFeed WHEN 1 THEN 'T' ELSE 'F' END
FROM OPENJSON(@json)
  WITH (
    id int,
    name varchar(100),
    description varchar(1000),
    shippingFreeze bit,
    receivingFreeze bit,
    mouseNorovirus bit,
    irradiatedFeed bit
  ) j;
";
    using (var sqlCmd = new SqlCommand(query, sqlConn))
    {
        sqlCmd.Parameters.Add("@json", SqlDbType.NVarChar, -1).Value = result; 
        await sqlCmd.ExecuteNonQueryAsync();
    }
}
catch (Exception ex)
{
    Dts.TaskResult = (int)ScriptResults.Failure;
}
finally
{
    if(sqlConn != null)
        cm.ReleaseConnection(sqlConn);
}

Notes:

• sqlCmd.CommandType = CommandType.Text is unnecessary.
• ReleaseConnection needs to be in a finally
• Although it's unclear why you are using ConnectionManager in the first place. You should probably create the SqlConnection directly, and put it in a using
• Avoid AddWithValue, instead specify types and lengths explicitly.
• Use Async versions of code with await. Do not call .Result or you may deadlock.