C# API Call for SSIS

Question

I am working on an API project the pulls from a API Rest using SSIS. This is a personal project and I do not have the ability to purchase a 3rd party software.

I have been working with C# to pull from the API as I know that it is capable of doing it, but I do not have the C# experience to be able to finish it.

Can anyone help me get the final touches on the code to pull back from the API? All the variables are objects, and the ExportDestination variable is just a file path for the export.

Here are the API docs if you need them: http://api.sc2replaystats.com/docs/index.html

Code :

#region Help:  
Introduction to the script task
#endregion

#region Namespaces
using System;
using System.Net;
using System.Text;
using System.IO;
#endregion

namespace ST_32488ae1d3e348ca83a1017e2e2bc39f
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : 
Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public string Authorization { get; private set; }
#region Help:  Using Integration Services variables and parameters in a script
#endregion
#region Help:  Firing Integration Services events from a script
#endregion
#region Help:  Using Integration Services connection managers in a script
#endregion
public void Main()
    {

        string errorLogPath = Dts.Variables["User::ErrorLog"].Value.ToString();
        // TODO: Add your code here
        try
        {

            string apiExportResponse = Dts.Variables["User::ExportDestination"].Value.ToString();

            string strResponse = SC2REPLAY("http://api.sc2replaystats.com/player/search");
            File.WriteAllText(apiExportResponse, strResponse);



        }
        catch (Exception ex)
        {
            ErrorLogging(ex, errorLogPath);
            Dts.Variables["User::APIReadFullResponse"].Value = ex.Message.ToString();

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

    public string SC2REPLAY(string Config)
    {

        var request = (HttpWebRequest)WebRequest.Create(Config);
        var encoding = new UTF8Encoding();
        Authorization += "8c517814830a1d19d7b39c3c3a0ac65e914bbbaf;d54f614345505a473b8ed71665666251c3061460;1560313648";//The API token specific to your REDCap project (each token is unique to each user for each project)
        var postData = "&players_name=" + "PartinG";
        //postData += "&players_name=" + "PartinG";
        byte[] data = encoding.GetBytes(postData);

        request.Method = "POST";//Supported Request Method
        request.ContentType = "application/x-www-form-urlencoded";
        request.ContentLength = data.Length;

        using (var stream = request.GetRequestStream())
        {
            stream.Write(data, 0, data.Length);
        }

        var response = (HttpWebResponse)request.GetResponse();
        Dts.Variables["User::APIReadResponseStatusCode"].Value = response.StatusCode;
        Dts.Variables["User::APIReadFullResponse"].Value = response.StatusDescription.ToString();


        return new StreamReader(response.GetResponseStream()).ReadToEnd();

    }

    public static void ErrorLogging(Exception ex, string errorLogPath)
    {

        string errorLog = errorLogPath + DateTime.Today.ToString("MM-dd-yyyy");



        if (!File.Exists(errorLogPath))
        {
            File.Create(errorLogPath).Dispose();
        }
        using (StreamWriter sw = File.AppendText(errorLogPath))
        {
            sw.WriteLine("=============Error Logging ===========");
            sw.WriteLine("===========Start============= " + DateTime.Now);
            sw.WriteLine("Error Message: " + ex.Message);
            sw.WriteLine("Stack Trace: " + ex.StackTrace);
            sw.WriteLine("===========End============= " + DateTime.Now);
            sw.Flush();
            sw.Close();
        }
    }


#region ScriptResults declaration
enum ScriptResults
    {
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    };
    #endregion

}
}

Edited to include error:

=============Error Logging ===========
===========Start============= 6/16/2020 12:42:09 PM
Error Message: The remote server returned an error: (401) Unauthorized.
Stack Trace:    at System.Net.HttpWebRequest.GetResponse()
at ST_32488ae1d3e348ca83a1017e2e2bc39f.ScriptMain.SC2REPLAY(String Config)
at ST_32488ae1d3e348ca83a1017e2e2bc39f.ScriptMain.Main()
===========End============= 6/16/2020 12:42:09 PM

It for some reason does not allow it to push through due to the authorization which is a header. — Tdakers, Jun 16 '20 at 13:30
Please edit the question and add the exact error message that you are getting. — Nick.Mc, Jun 16 '20 at 13:40
Also many people make the mistake of thinking that data integration solutions must be built in SSIS. In my opinion, if a web API is involved you’re often much better just building in the standard C# platform. SSIS introduces a lot of needless difficulties. You don’t need SSIS to load data into SQL. — Nick.Mc, Jun 16 '20 at 13:45
@Nick.McDermaid I'm not super versed in the art of C#. Do you have a way that could be used to import the data into SQL Directly dynamically? I know their is a package called RestSharp that Postman uses that I was able to pull back the data in Visual Studios, but 1. I didn't know how to put it into a file/SQL Server 2. How to make it dynamic where it would reference several different ReplayIDs — Tdakers, Jun 16 '20 at 14:17
In this code, your API call result is in the variable `strResponse`. There are many examples online of how to write code that connects to SQL Server and writes a string value to a table. You still need to solve your authorisation problem though. — Nick.Mc, Jun 17 '20 at 11:12
Either the authorisation header isn't set up properly or the key you are using isn't valid. If this works in PostMan, try to compare the messages between PostMan and this — Nick.Mc, Jun 17 '20 at 11:29
I now that the authorization isn't set up correctly. I think the main thing I need at this point is to figure out why the authorization isn't being set as a header. Thats really what I need the most help with. — Tdakers, Jun 17 '20 at 13:11
A C# expert would be better but... I don't see you applying the token (in `Authorization`) to the call anywhere. You need to add something like `request.Headers.Add("Authorization", "Bearer " + Authorization);`. The exact format depends on the REST API — Nick.Mc, Jun 17 '20 at 14:01
I found some examples here https://stackoverflow.com/questions/21158298/how-to-force-webrequest-to-send-authorization-header-during-post and here https://stackoverflow.com/questions/14627399/setting-authorization-header-of-httpclient authorisation headers are a pain. If you don't get them perfect they don't work. Sometimes you need _Bearer_ in them sometimes you don't. That's why you need to inspect whatever PostMan is generating — Nick.Mc, Jun 17 '20 at 14:05
@Nick.McDermaid Thanks for the input! I'll have to try that when I get a chance. I am certainly no C# expert haha. If I could use what Postman generated I would certainly do that, but that uses RestSharp which doesn't play well with SSDT in SSIS — Tdakers, Jun 17 '20 at 16:26

C# API Call for SSIS

Code :

0 Answers0