How To Consume Web API Through SSIS Package

SSIS (SQL Server Integration Service) is a data migration software which is used to extract, transform, and load the data. Apart from extracting and loading the data, SSIS can also call the API to perform a certain task. Let’s take a scenario where the failure emails are stored in the database, and it has to be initiated in the night. Now, the problem is, we don’t have any UI screen to initiate these failure emails. So, we usually go with SQL jobs which are configured to run the SSIS packages on a scheduled basis.

While working with SSIS, we can use a script task to write the custom C# code. The Web Service and WCF service can be integrated through script task. We can use the WSDL file and consume these services. We can also use the project parameter to configure the Service URL based on the environment such as Dev, Staging, and production etc. The benefit of parameterizing value is: it can be configured through environment variable on SSISDB (the place where we deploy our SSIS packages). So, it will be easy to change the environment for packages whenever is required.

In this article, I have illustrated the purpose of SSIS with Web API. It shows how SSIS can use Web API to do certain operations, like updating the database table based on Service response, and process the data, etc.

Not only Web API, SSIS can consume the following services as well.

• Web Service
• WCF service
• .NET Core API

In my previous article, I have explained how to consume web service using SSIS and found that there is not much difference while consuming the WCF as well. It will be the same as Web Service; just add the WCF service reference to your SSIS script task, and consume the way you have consumed Web Service.

Here, I have used Service URL as a project parameter, so you can use the same in other SSIS packages to consume services as per your requirement.

I just want to highlight ASP.Net core as well, so it will easy for you to consume it as you will be aware to use API with SSIS. So I believe this article will help you to understand the way of consuming Web API and Core API using SSIS scrip task.  

Let’s say we have one Web API service running in IIS and it exposes ProcessData() method via ProcessManager controller. It accepts the amount and transaction ID to be processed further. It does some business functionality internally and sends details to a third party service to get PNREF number. It provides the PNREF number to the client through response object.

More about Web API

It’s a framework on top of .NET framework which provides an easy way to build HTTP services, and these services can be consumed by a broad range of clients including bowsers and mobiles. It’s a platform to build RESTful services.

Solution

To consume Web API into the SSIS package, first, create the SSIS application. You should have SSDT tool installed into your system. I have used SSDT 2015 in my sample application.

Perform the following steps,

1. Create package (package.dtsx) inside the SSIS project.
2. Go the solution explorer and double click on project.params. It will provide you the screen to add project level parameters. (refer below screen)
   
   
   
   
3. Drag the script task from SSIS toolbox on package design screen. Double click on the script task. It will open the window where you can configure the ReadOnlyVariables. Set project parameters.
   
   
   

4. Click on Edit Script.
5. It will open the VSTA project where you can write the C# custom code.
6. Go to the Solution Explorer.
   
   
   
   
7. Right click on Reference
8. Click on add the reference and add the Net.Http assembly.
9. Add the System.Net.Http.Formatting from the NuGet packages or from the Package Manage Console.
   
   If you are installing through PMC then write below code.
   
   Install –Package Microsoft.AspNet.WebApi.Client
   
   It will add the required libraries the project.
   
   
   

10. 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 libraries 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.

11. Open Visual Studio command prompt and register the following DLL.
    
    
    
    You can use PowerShell script also to register the DLL’s into the GAC.

12. After adding into the GAC, you can again open SSIS package script task and check whether the references are restored or not.
    
    
    

13. 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
    
        }
    
    }

14. When you debug the SSIS package, you get the response from Web API (refer 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.