locked
Consume REST Api thorugh Script task component RRS feed

  • Question

  • Hi all,

    I have one question regarding to Consume REST API without using NewtonJson library or third party tool. I created paresJson function and stored proc to insert data into sql table. But first step which i am trying to call REST api i am not able to resolve that section.

    1. Consume REST api stored result into jsonstring 

    2. Call storedproc example (sqlCommand.CommandText = "Exec" + storedproc + jsonString)

    3. When i will execute stored proc from sql management studio then rest api -> parseJson function-> insert json data into sql table.

    I am having issues in the first process. 

    I will be thanksful for your effort and help.

    Code : what i did till this time

    string wUrl = Dts.Variables["User::Extratrafficapi"].Value.ToString();
                string storedproc = Dts.Variables["User::storedproc"].Value.ToString();
                string json = Dts.Variables["User::json"].Value.ToString();


                var webRequest = System.Net.WebRequest.Create(wUrl);
                
                if (webRequest != null)
                {
                    webRequest.Method = "GET";
                    webRequest.Timeout = 12000;
                    webRequest.ContentType = "application/json";               
                    HttpWebResponse webResponse = (HttpWebResponse)webRequest.GetResponse();
                    var webResponseStream = webResponse.GetResponseStream();
                    if (webResponseStream != null && webResponseStream != Stream.Null)
                    {
                        using (System.IO.Stream s = webRequest.GetResponse().GetResponseStream())
                        {

                            string jsonString;

                            using (System.IO.StreamReader sr1 = new System.IO.StreamReader(s))
                            {
                                jsonString = sr1.ReadToEnd().Replace("\\", "");
                                //jsonString = sr1.ReadToEnd();
                                sr1.Close();
                            }

                           

                           
                            string consString = @"Data Source=servername;Initial Catalog=databasename;Integrated Security=True;";
                            using (SqlConnection con = new SqlConnection(consString))
                            {
                                con.Open();
                                SqlCommand sqlCommand = new SqlCommand();
                                sqlCommand.Connection = con;
                                sqlCommand.CommandType = CommandType.StoredProcedure;
                                sqlCommand.CommandText = "Exec" + storedproc + jsonString;
                                sqlCommand.ExecuteNonQuery();
                                MessageBox.Show(sqlCommand.CommandText);
                            }
                        }
                    }
                }           

                     Dts.TaskResult = (int)ScriptResults.Success;
    }

    Regards,

    Shilpi kumari

    Thursday, September 3, 2020 11:31 PM

All replies

  • Did you try to investigate the issue using a separate program in Visual Studio 2019? For example, create a new “Console App (.NET Framework)” project, paste the code that corresponds to first task, but use some constant strings instead of Dts.Variables. Then execute step-by-step using Debugger, check the value of jsonString.

    But, if this is Integration Service (SSIS), maybe there are components that perform these tasks.

    Friday, September 4, 2020 8:09 AM
  • If the JSON is simple then parse it by hand but if not then you can get Newtsoft.Json to work in SSIS if you install the assembly into the GAC first. This is a heavy handed approach but it works. Refer to this thread about how to do it.

    Alternatively the JavaScriptSerializer is a legacy .NET class that can parse JSON and is available in SSIS. The challenge is that it requires the data model to be properly attributed. But that shouldn't be a big deal on JSON responses.

    The preference is to use System.Text.Json but I don't know if this new assembly is available in your version of SSIS yet.


    Michael Taylor http://www.michaeltaylorp3.net

    Friday, September 4, 2020 2:49 PM
  • Hi friend,

    Thanks for your suggestion.

    I tried it is working on script task but now i am getting timeour error

    Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
    The statement has been terminated.

    Regards,

    Shilpi


    shilpi

    Thursday, September 10, 2020 6:21 AM
  • Hi friend,

    Thanks so much for your valuable point. I tried i can jsonstring but when i am calling stored proc to execute i got.

    Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
    The statement has been terminated.

    Regards,

    Shilpi


    shilpi

    Thursday, September 10, 2020 6:25 AM
  • Your code contains ‘webRequest.Timeout = 12000’. Do you receive timeout error after 12 s? Which line generates the error?


    • Edited by Viorel_MVP Thursday, September 10, 2020 8:14 AM
    Thursday, September 10, 2020 8:14 AM