通過SSIS腳本任務將來自API的回應插入到SQLServer表中

我正在嘗試創建一個呼叫 REST API 并將回應插入 Azure SQL 服務器表的 SSIS 包。為此，我使用腳本任務和 ADO.NET 連接管理器。API 回應采用 JSON 格式，如下所示

[{"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"}]

在腳本任務下面，我嘗試呼叫 REST API 并將回應轉換為 JSON，但我不確定如何遍歷每個 JSON 記錄并將它們插入將 JSON 中的值映射到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;
        }
    }

這是我第一個使用 SSIS 腳本和 API 呼叫的專案，非常感謝任何幫助

uj5u.com熱心網友回復：

res_JSON您可以按以下代碼段示例代碼進行迭代：

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

或者為了可讀性，最好有類似的類json：

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

和as陣列deserialize：resultJsonObj

var res_JSON = JsonConvert.DeserializeObject<JsonObj[]>(result);
foreach (var obj in res_JSON)
{
    //obj.id
    //obj.name
    //...
}

標籤：C＃ sql服务器 西斯 脚本任务

上一篇：選擇每組的第一條和最后一條記錄

下一篇：SQL計算時間差