How To Consume Web Service Through SSIS Package

Background

As we know, SSIS (SQL Server Integration Service) is a data migration software used to extract, transform, and load the data. I am not going to explain what SSIS is and what its features are, but yes, this article illustrates the purpose of SSIS with Web Service. It’s all about how SSIS can use Web Service to perform certain operations, like updating the database table based on Service response or process the data, etc.

Not only Web Service but SSIS can consume the following services as well:

• WCF service
• Web API
• Net Core API

These are the different services provided by Microsoft .NET technology. I will explain how to consume these services through SSIS package in my next article.

Let’s say we have one Web Service running in IIS and it exposes the ProcessData() method. It does some business functionality internally and sends the details to a third party service. It provides the reference number to the client. (Refer the below screen.)

More about Web Service

It’s an XML service and runs on HTTP & HTTPS protocol. It accepts the XML request and provides the response in XML format.

Solution

To consume the Web Service into the SSIS package, first, create the SSIS application. You should have SSDT (SQL Service Data Tools) installed on your system. I have used SSDT 2015 in my sample application.

Follow the below steps.

1. Create package (package.dtsx) inside the SSIS project.
2. Drag the script task from SSIS toolbox on "Package Design" screen (refer the below screen).
   
   
   

3. Double click on "Script Task".
4. Click on "Edit Script".
   
   
   
   
5. It will open the VstaProjects where you can write the C# custom code.
6. Go to the Solution Explorer.
   
   
   
   
7. Right click on Reference.
8. Give the Web Service URL and add into the project (refer the below screen).
   
   

9. Follow the below code. Here, I am passing the transactionID and amount to the ProcessData().
   
   
   1. #region Namespaces
   2. using System;
   3. using System.Data;
   4. using Microsoft.SqlServer.Dts.Runtime;
   5. using System.Windows.Forms;
   6. #endregion
   8. namespace ST_c02ad868b1314c3895bebfedd71e7fbf
   9. {
   10. /// <summary>
   11. /// ScriptMain is the entry point class of the script. Do not change the name, attributes,
   12. /// or parent of this class.
   13. /// </summary>
   14. [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
   15. public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
   16. {
   18. /// <summary>
   19. /// This method is called when this script task executes in the control flow.
   20. /// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
   21. /// To open Help, press F1.
   22. /// </summary>
   23. public void Main()
   24. {
   25. // the below detail can be pulled from database and send to the service.
   26. long transactionID = 110011;
   27. decimal amount = 500.0m;
   29. ServiceManager.DemoService demoService = new ServiceManager.DemoService();
   30. try
   31. {
   32. var result = demoService.ProcessData(transactionID, amount);
   33. //based on result or status you can update the required table.
   34. //TODO
   35. Dts.TaskResult = (int)ScriptResults.Success;
   37. }
   38. catch (Exception ex)
   39. {
   41. Dts.TaskResult = (int)ScriptResults.Failure;
   42. }
   43. }
   45. #region ScriptResults declaration
   46. /// <summary>
   47. /// This enum provides a convenient shorthand within the scope of this class for setting the
   48. /// result of the script.
   49. ///
   50. /// This code was generated automatically.
   51. /// </summary>
   52. enum ScriptResults
   53. {
   54. Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
   55. Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
   56. };
   57. #endregion
   59. }
   60. }

10. When you debug the SSIS package, you get the response from Web Service (refer the below screenshot).
    
    
    

Conclusion

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