How To Consume Web API Through SSIS Package

• Create a package (package.dtsx) inside the SSIS project.
• Go to the solution explorer and double-click on the project. params. It will provide you with the screen to add project-level parameters. (refer to below screen).
  
• Drag the script task from the SSIS toolbox on the package design screen. Double-click on the script task. It will open the window where you can configure the ReadOnlyVariables. Set project parameters.
  
• Click on Edit Script.
• It will open the VSTA project where you can write the C# custom code.
• Go to the Solution Explorer.
  
• Right-click on Reference.
• Click on Add the reference and add the Net. Http assembly.
• Add the System.Net.Http.Formatting from the NuGet packages or from the Package Manage Console.
• If you are installing through PMC then write the below code.

  Install-Package Microsoft.AspNet.WebApi.Client
  

• It will add the required libraries the project.
  
• After adding the library references, you can save the project and come out to verify whether these libraries are referenced properly or not.
• What happens here, when you go out from the project, is it lost the library references. You can see in the below screen where Newtonsoft.json and System.Net.Http.Formatting DLL are missing the references.
  
• To avoid this error, we need to register these DLL into GAC, so VSTA project can take reference directly.
• Open the Visual Studio command prompt and register the following DLL.
  
• You can use the PowerShell script also to register the DLLs into the GAC.
• After adding to the GAC, you can again open the SSIS package script task and check whether the references are restored or not.
  
• Follow the below code. Here, I am passing the transactionID and amount to the ProcessData().

  #region Namespaces
  using System;
  using System.Data;
  using Microsoft.SqlServer.Dts.Runtime;
  using System.Windows.Forms;
  using System.Net;
  using System.Net.Http;
  using System.Net.Http.Headers;
  using ServiceManager.API;
  #endregion
  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
      }
  }
  

• When you debug the SSIS package, you get the response from Web API (refer to the below screen).
  

Conclusion

SSIS provides the features by using a script component to consume the Web API. And, it can be achieved by writing the custom code in SSIS.