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.
- Create package (package.dtsx) inside the SSIS project.
- Drag the script task from SSIS toolbox on "Package Design" screen (refer the below screen).
- Double click on "Script Task".
- Click on "Edit Script".
- It will open the VstaProjects where you can write the C# custom code.
- Go to the Solution Explorer.
- Right click on Reference.
- Give the Web Service URL and add into the project (refer the below screen).
- 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;
- #endregion
-
- namespace ST_c02ad868b1314c3895bebfedd71e7fbf
- {
-
-
-
-
- [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
- public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
- {
-
-
-
-
-
-
- public void Main()
- {
-
- long transactionID = 110011;
- decimal amount = 500.0m;
-
- ServiceManager.DemoService demoService = new ServiceManager.DemoService();
- try
- {
- var result = demoService.ProcessData(transactionID, amount);
-
-
- Dts.TaskResult = (int)ScriptResults.Success;
-
- }
- catch (Exception ex)
- {
-
- Dts.TaskResult = (int)ScriptResults.Failure;
- }
- }
-
- #region ScriptResults declaration
-
-
-
-
-
-
- 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 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.