Unable to insert data from the JSON in to the SQL server table
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;
}
}
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.Textis unnecessary.ReleaseConnectionneeds to be in afinally- Although it's unclear why you are using
ConnectionManagerin the first place. You should probably create theSqlConnectiondirectly, and put it in ausing - Avoid
AddWithValue, instead specify types and lengths explicitly. - Use
Asyncversions of code withawait. Do not call.Resultor you may deadlock.