Resolved SSIS POST JSON to API

[Beanymattd]

Good morning,

I have some JSON format files and have a requirement to POST these to an API using C# inside SSIS, payloads are sent via HTTPS. The payloads are to be sent with the Content-Type header of "application/json" and the UserAPI-Key header.

Successful requests can return 200 and 201. Failures can return 400, 403, and 404. In the event of an error 400, the payload will continue, logging, where possible that this file failed. In the event of an error 403, or 404, the process will halt under an error condition.

Below is my JSON payload format to be sent (multiple files like this):

    {

        "Time":"2018-10-21T00:08:03",

        "Control_Code": "JKX",

        "metrics": [

            {

                "Metric": 3721,

                "Organisation":"AD450",

                "Value": 20,

                "Datetime":"2018-10-21T00:08:00"

            },

            {

                "Metric": 1234,

                "Organisation":"HG650",

                "value": 88,

                "datetime":"2018-10-21T00:08:00"

            }

          

        ]

    }

I have the following code which seemed like a good starting point, however i'm unsure due to my inepxerience if this is suitable and can be adapted to my requirements. Any help is greatly appreciated as always:

public void Main()
        {
            string response = "";
            string json = Dts.Variables["User::FileName"].Value.ToString();
            string url = "https://your.url.com";

            var httpWebRequest = (HttpWebRequest)WebRequest.Create(url);
            httpWebRequest.Headers.Add("Authorization", "Basic your-auth-info");
            httpWebRequest.ContentType = "application/json";
            httpWebRequest.Method = "POST";
            httpWebRequest.Timeout = 600000;
            using (var streamWriter = new StreamWriter(httpWebRequest.GetRequestStream()))
            {
                streamWriter.Write(json);
                streamWriter.Flush();
                streamWriter.Close();
            }

            var httpResponse = (HttpWebResponse)httpWebRequest.GetResponse();
            using (var streamReader = new StreamReader(httpResponse.GetResponseStream()))
            {
                response = streamReader.ReadToEnd();
            }
            httpResponse.Close();
        }

 

[Skydiver]

Directly using the HttpWebRequest and HttpWebResponse classes is the old way of doing things. Most modern code would have you use the HttpClient class instead because it provides better abstraction to let you focus on your data rather then the mechanics of moving the data around. (Yes, under the covers, the HttpClient eventually falls back to use HttpWebRequest.)

It would be really anachronistic to see code that uses the old HttpWebRequest's directly but be transporting around modern JSON data with a REST protocol. Traditionally, you would see code that uses old HttpWebRequest playing with XML data and using SOAP as the protocol.

 

[JohnH]

Documentation is even more direct:

Important
We don't recommend that you use HttpWebRequest for new development. Instead, use the System.Net.Http.HttpClient class.

 

[Beanymattd]

1. Thanks for the replies, looking into HTTP Client I found the following? Am I on the right lines?
2. 
   
3. namespace ST_9739a03d571d4befabf178f702504b47   
   {   
       /// <summary>   
       /// ScriptMain is the entry point class of the script.  Do not change the name, attributes,   
       /// or parent of this class.   
       /// </summary>   
       [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]   
       public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase   
       { 
           /// <summary>   
           /// This method is called when this script task executes in the control flow.   
           /// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.   
           /// To open Help, press F1.   
           /// </summary>   
           public void Main()   
           {   
               try   
               {   
                   string serviceUrl = Dts.Variables["$Project::ServiceUrl"].Value.ToString();   
                   HttpClient client = new HttpClient();   
                   client.BaseAddress = new Uri(serviceUrl);   
                   // Add an Accept header for JSON format.   
                   client.DefaultRequestHeaders.Accept.Add(   
                       new MediaTypeWithQualityHeaderValue("application/json"));   
       
                   decimal amount = 1200.50m;   
                   long tranactionID = 1001;   
                   string APIUrl = string.Format(serviceUrl + "/ProcessManager/ProcessData?amount={0}&transactionID={1}", amount, tranactionID);   
                   var response = client.GetAsync(APIUrl).Result;   
                   if (response.IsSuccessStatusCode)   
                   {   
                       var result = response.Content.ReadAsAsync<Response>().Result;   
                       if (result.IsSucess)   
                       { 
                           //TODO update your database based on the result   
                       }   
                       Dts.TaskResult = (int)ScriptResults.Success;   
                   }   
               }   
               catch (Exception ex)   
               {   
                   Dts.TaskResult = (int)ScriptResults.Failure;   
               }   
           }   
           #region ScriptResults declaration   
           /// <summary>   
           /// This enum provides a convenient shorthand within the scope of this class for setting the   
           /// result of the script.   
           ///     
           /// This code was generated automatically.   
           /// </summary>   
           enum ScriptResults   
           {   
               Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,   
               Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure   
           };   
           #endregion 
       } 
   }

 

[Skydiver]

Yes, that should give you a starting point.

The code above does a GET. You'll need to change things to perform a POST.

You'll need to make your exception handling more fine grained so that you can catch the 400 vs 403/404 errors.