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;
        }
    }

A few issues are apparent: 1) I'm surprised JsonConvert is successfully parsing the example JSON because it's invalid JSON: it's an object containing an array that has no property name! 2) you're reusing the same SqlCommand object in each iteration over res_JSON... and appending new SqlParameters to the same command in each iteration! 3) AddWithValue() should be avoided, prefer to use the Add overloads that use the exact SqlDbType parameters along with type sizes - in this case, the length of each related nvarchar column. — AlwaysLearning
– AlwaysLearning, Commented Apr 6, 2022 at 12:16
@KrishnaVarma Thank you.. That fixed the issue :-) Appreciate your help — user4912134
– user4912134, Commented Apr 6, 2022 at 12:57
By the way client.GetAsync(APIUrl).Result should be await client.GetAsync(APIUrl) — Charlieface
– Charlieface, Commented Apr 6, 2022 at 15:49

Charlieface · Accepted Answer · 2022-04-06 15:52:13Z

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.

edited Apr 6, 2022 at 15:52

answered Apr 6, 2022 at 13:18

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user4912134 Over a year ago

Thank you so much for the help.. I tried using the finally block as you suggested but I keep getting {"The connection was not closed. The connection's current state is open."}

user4912134 Over a year ago

I added the updated code. Can you please tell me if I am missing something

user4912134 Over a year ago

Also On one of the table field values I was trying to (Convert.ToString(jsonObj.name)).Split()[0]) pass the part of the string from the JSON field, Is this doable with the OPENJSON ?

Charlieface Over a year ago

Looks like AquireConnection opens it anyway. Yes you could do that, probably something like ISNULL(LEFT(name, NULLIF(CHARINDEX(' ', name), 0) - 1), name) in the select

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related