SSIS C# Script for Requesting Bearer Token

July 19, 2024
|
SSIS

Have you ever come across a situation where you are using third party tools to facilitate a process, and for one reason or another it will not co-operate and JUST WORK, automagically!?!

I came across a situation like that just this week.

The ask…. automatically capture a bearer token and use it to authenticate to an API. Simple right? Not quite, considering I do not have experience with APIs. Failure is not an option. The only option is to learn and do it.

I received a quick tutorial on reading and understanding API documentation, from an internal resource, familiar with API development. I also received a quick tutorial on Postman. Fortunately, there are plenty of videos on how to use Postman and making requests to APIs. Armed with this knowledge, I proceeded to apply what I learned to SSIS and the third-party app.  I discovered that all I needed to do was enter the CURL statement generated by Postman into the third-party application HTTP Request connector, and viola, done! Mission accomplished! Not quite. There was something about the way the third-party app was sending the request that the target did not accept.

Well, after multiple attempts at different configurations and playing around with altering the CURL statement and reading the vendors documentation, again,again, again, and again. And performing the obligatory Google searches on the error, amazingly I found zero in terms of helpful information, nada, zilch,…bupkus.  But gentle reader, be not dismayed. There is always a plan B. Well, I was thinking, we need a plan B. Fortunately,the tech gods blessed me with a problem-solving mind, and I thought, hey you can do this in a C# script task.

Enough with the background! I know you just want to know how I did it in a C# script task. Well gentle reader here you are:

The overall process. The script tasks make a request to the API token endpoint using a client id and client secret. The API responds with a bearer token. The bearer token is valid and has a lifespan of 60 minutes. You can populate a variable and do whatever you wish with the captured value. The C# script is designed to obtain an OAuth2 bearer token from an API endpoint using client credentials (client ID and client secret) provided as SSIS variables. You will need to create package level variables that you will reference in your SSIS script. One read only variable for each of the following:  token endpoint, client id, and client secret.You will also need to create a read write variable to store the bearer token value.

 

Let us take this step by step.

  1. Variable     Initialization:
       
    • The script      initializes several variables such as accessTokenUrl, clientId, and clientSecret      by fetching their values from SSIS package variables (User::Token      Endpoint variable, User::Client ID Variable, User::Client Secret Variable).
    2.  
  2. Encoding Client     Credentials:
       
    • The client ID      and client secret are combined and Base64 encoded (base64Credentials).      This encoded string is then used for Basic Authentication.
    3.  
  3. Setting SSL/TLS     Protocol:
       
    • It ensures      that the HTTPS request uses a secure protocol (TLS 1.2 or higher) for      communication with the API endpoint.
    4.  
  4. Creating HTTP     Request:
       
    • It creates an HttpWebRequest      object to interact with the API endpoint.
      •  
    • Sets the      method to POST since it is requesting a new access token.
      •  
    • Sets the      content type to application/x-www-form-urlencoded, which is typical for      OAuth2 token requests.
    5.  
  5. Adding Basic     Authentication Header:
       
    • Adds an      Authorization header with the encoded client credentials (base64Credentials).
    6.  
  6. Form Data for     Request:
       
    • Constructs the      form data (formData) required for the token request. In this case, it      specifies grant_type=client_credentials, indicating that the client is requesting      access using its own credentials.
    7.  
  7. Writing Data to     Request Stream:
       
    • Converts formData      into a byte array (byteArray) and writes it to the request stream using request.GetRequestStream().
    8.  
  8. Handling API     Response:
       
    • Sends the      request to the API endpoint and retrieves the response (response).
      •  
    • Reads the      response stream (responseStream) and parses the JSON response to extract      the access_token.
      •  
    • Stores the      obtained access_token into an SSIS variable (Bearer Token Variable).
    9.  
  9. Error Handling:
       
    • Includes      exception handling to manage potential errors like HTTP errors (WebException)      or general exceptions (Exception).
      •  
    • Fires      appropriate SSIS events (FireError) to log errors encountered during the      script execution.
    10.  
  10. Script     Completion:
       
    • Sets Dts.TaskResult      to Success if the script executes without errors, otherwise sets it to Failure.

This script automates the process of fetching an OAuth2 token usingclient credentials authentication and integrates it into an SSIS (SQL ServerIntegration Services) package workflow, where the token can be used in additionaltasks requiring authentication with external until the token expires.

 

This is what the code for the script task should look like.

 

 

 

#region Namespaces

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Runtime;

using System.Windows.Forms;

using System.Net;

using System.IO;

using System.Text;

using Newtonsoft;

using Microsoft.CSharp;

#endregion

 

namespace ST_2d2e488f301349dfbd7cc328e636a7d1

{

   ///<summary>

   /// Script Main 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

           {  

   

       public void Main()

       {          try

           {

               //URL of the API endpoint to obtain the token

              string accessTokenUrl = Dts.Variables["User::Token Endpointvariable"].Value.ToString();

 

               //Client ID and Client Secret

              string clientId = Dts.Variables["User::Client ID Variable"].Value.ToString();

              string clientSecret = Dts.Variables["User::Client Secret Variable"].Value.ToString();

 

               //Encode the client credentials to Base64

              string base64Credentials =Convert.ToBase64String(Encoding.ASCII.GetBytes($"{clientId}:{clientSecret}"));

 

             

               //Verify and set the appropriate SSL/TLS protocol version in your C# script.

               //Many APIs and servers now require TLS 1.2 or higher for secure connections.

               //Explicitly set this in the  script beforemaking the HTTPS request

              ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12 |SecurityProtocolType.Tls11 | SecurityProtocolType.Tls;

 

               //Create a WebRequest

              HttpWebRequest request =(HttpWebRequest)WebRequest.Create(accessTokenUrl);

              request.Method = "POST";

              request.ContentType = "application/x-www-form-urlencoded";

 

               //Add Basic Authentication header

              request.Headers.Add("Authorization", "Basic " +base64Credentials);

 

               //Form data to send in the request (x-www-form-urlencoded format)

              string formData = "grant_type=client_credentials";

 

               //Encode the form data into a byte array

              byte[] byteArray = Encoding.UTF8.GetBytes(formData);

              request.ContentLength = byteArray.Length; // Set the Content-Lengthheader

 

               //Write the data to the request stream

               using(Stream dataStream = request.GetRequestStream())

               {

                  dataStream.Write(byteArray, 0, byteArray.Length);

               }

 

               //Get the response

               using(WebResponse response = request.GetResponse())

               {

                  using (Stream responseStream = response.GetResponseStream())

                   {

                      using (StreamReader reader = new StreamReader(responseStream))

                      {

                          // Read the response and extract the token

                          string responseJson = reader.ReadToEnd();

                          dynamic json Response =Newtonsoft.Json.JsonConvert.DeserializeObject(responseJson);

 

                          // Assuming the token is in the 'access_token' field

                          string bearerToken = jsonResponse.access_token;

 

                          // Store the token in SSIS variable

                          Dts.Variables["Bearer Token Variable"].Value = bearerToken;

                          //MessageBox.Show((string)Dts.Variables["Bearer Token Variable"].Value);

 

 

                      }

                   }

               }

 

              Dts.TaskResult = (int)ScriptResults.Success;

           }

           catch(WebException webEx)

           {

               //Handle HTTP errors

               if(webEx.Response is HttpWebResponse response)

               {

                  if (response.StatusCode == HttpStatusCode.Unauthorized)

                   {

                      Dts.Events.FireError(0, "Error in Script Task","Unauthorized: Check client credentials or authentication method.",string.Empty, 0);

                   }

                  else

                   {

                      Dts.Events.FireError(0, "Error in Script Task", $"HTTPError: {(int)response.StatusCode} - {response.StatusDescription}",string.Empty, 0);

                   }

               }

               else

               {

                  Dts.Events.FireError(0, "Error in Script Task", webEx.Message,string.Empty, 0);

               }

 

              Dts.TaskResult = (int)ScriptResults.Failure;

           }

           catch(Exception ex)

           {

               //Handle other exceptions

              Dts.Events.FireError(0, "Error in Script Task", ex.Message,string.Empty, 0);

              Dts.TaskResult = (int)ScriptResults.Failure;

           }

       }

 

       #regionScriptResults declaration

       ///<summary>

       /// This enum provides a convenient shorthand within the scope of this class for setting the

       /// result of the script.

       ///

       /// This codewas generated automatically.

       ///</summary>

       enum ScriptResults

       {

           Success =Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,

           Failure =Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

       };

       #endregion

 

           }

}

 

I hope this script proves useful.

 

‍

← View all posts