Write C# Code to get the data from Rest API using SSIS Script Task

Question

Hello All,
I am a SQL Developer, and I don't have a good knowledge on C# Code.
Can you please help me on how to write the code in getting the data by calling a Rest API and storing the data in a SQL Server table?

Below is the URL:
curl-X GET-H "Content-Type:application/json"-u <username>:<password> "https://agent-pos-na1.fourth.com/CompanyID/controllers/vertx/hotschedules/getConcepts"

Below are the output columns and its datatypes.

Key Type Description
extId Number Concept external ID
name String Concept Name

FYI... I Have the CompanyID( It is a parameter), I have the UserName and Password details also with me.
My plan is to keep the URL, parameters, Credentials in the Variables and use them in the C# Code.

I tried to see the results by calling the API using Postman and I am able to achieve it.

Thank you so much in advance.

Answer 1

Was able to achieve it.
Below is the code for reference to others.

        try
        {
            string connetionString = null;
            string sql = null;
            var serializer = new JavaScriptSerializer();

            string serviceUrl = "APIURL";
        HttpClient client = new HttpClient();
        client.BaseAddress = new Uri(serviceUrl);



        var byteArray = Encoding.ASCII.GetBytes("UserName:Password");
        client.DefaultRequestHeaders.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("Basic", Convert.ToBase64String(byteArray));
        connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName; Trusted_Connection=True;";
        string APIUrl = "/controllers/vertx/hotschedules/getConcepts";
        var response = client.GetAsync(APIUrl).Result;
        if (response.IsSuccessStatusCode)
        {
            var result = response.Content.ReadAsStringAsync().Result;

                var dt = serializer.Deserialize<Dictionary<string,

                  string>[]>(result);

                using (SqlConnection cnn = new SqlConnection(connetionString))

                {

                    cnn.Open();

                    sql = "insert into dbo.Concept (ExtID,Name) values(@ExtID, @Name)";

                    foreach (var item in dt)

                    {

                        using (SqlCommand cmd = new SqlCommand(sql, cnn))

                        {

                            cmd.Parameters.Add("@ExtID", SqlDbType.NVarChar).Value = item["extId"].ToString();

                            cmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = item["name"].ToString();

                            int rowsAdded = cmd.ExecuteNonQuery();

                        }
                    }

                    cnn.Close();

                }

                Dts.TaskResult = (int)ScriptResults.Success;
        }
            //Dts.TaskResult = (int)ScriptResults.Success;
        }
        catch (Exception ex)

        {
            Dts.Events.FireError(0, "Fire Error", "An error occurred: " + ex.Message.ToString(), "", 0);
            Dts.TaskResult = (int)ScriptResults.Failure;
        }