My client sends a request to me that they want me to load data, which will be returned by Web Service APIs, and do some transformation before importing to database. In this article, I will provide an approach to load data with Web Service Task and XML Source components in SSIS.
We have many approaches to extract data from Web Service APIs. We can use .NET methods (C# , VB.NET,...) but we need to do more transformation and cleansing data so I determine to use Web Service Task and XML Source components in SSIS.
For the demonstration purpose, I assume that Web Service returns Employee data set. We will use Web Service Task to work with it and XML Source to read data.
Visual Studio 2010 (VS2010)
SQL Server 2014 Developer
SQL Server Data Tools for Visual Studio 2013
Step 1 - Create a staging table
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.[Employee_WS]') AND type in (N'U'))
CREATE TABLE dbo.Employee_WS(
[NationalIDNumber] [nvarchar](15) NOT NULL,
[LoginID] [nvarchar](256) NOT NULL,
[JobTitle] [nvarchar](50) NOT NULL,
[BirthDate] [date] NOT NULL,
[MaritalStatus] [nchar](1) NOT NULL,
[Gender] [nchar](1) NOT NULL,
[HireDate] [date] NOT NULL,
) ON [PRIMARY]
Step 2 - Create a Web Service Application
Create a Web Service Application in Visual Studio 2010 and name it as EmployeeListServices. VS2010 creates a new .asmx file in the project, I will rename it as GetEmployeeServices.asmx.
and create a new web service method by following code. The method returns list of employees in HumanResources.Employee
table in AdventureWorks201
. You must edit stConnectionOLEDB to meet your environment such as database server, database name, authentication mode,...
public XmlDocument GetEmployees()
var xml = "<?xml version=\"1.0\" encoding=\"utf-16\" ?>\n";
//string stConnectionOLEDB = @"Data Source=HSSSC1PCL01198\SQLSERVER2014;Initial Catalog=AdventureWorks2014;Provider=SQLNCLI11.0;Integrated Security=SSPI;";
string stConnectionOLEDB = @"Data Source=DUNGDT\SQLSERVER2014;Initial Catalog=AdventureWorks2014;Integrated Security=SSPI;";
string stQueryText = "SELECT NationalIDNumber,[LoginID],JobTitle,BirthDate,[MaritalStatus],[Gender],[HireDate] FROM HumanResources.Employee WHERE NationalIDNumber In(695256908,134969118) FOR XML RAW('Employee'),ROOT('Employees'),ELEMENTS;";
using (SqlConnection sqlcon = new SqlConnection(stConnectionOLEDB))
SqlCommand cmd = new SqlCommand(stQueryText, sqlcon);
SqlDataReader reader = cmd.ExecuteReader();
xml = reader.ToString();
XmlDocument xmldoc = new XmlDocument();
Run Web Service Application to make sure our method returns data. Press F5
Now, our Web Service is ready, it is located at http://localhost:49944/GetEmployeeServices.asmx
This URL will be changed based on your environment.
Step 3 - Create a SSIS package
Create a SSIS project and name it as LoadEmployeeByWebAPI. Then create a package LoadEmployee.dtsx
Create a variable
I create a variable to contain the path of a XML file, which will be used to store data returned by the Web Service
Design Data Flow
Drag and drop Web Service Task into Control Flow tab, name it as Get Data From Web Service
. Double-click to open Web Service Task Editor
On Input tab:
: click on New Connection to create a connection to Web Service, which we created before. HTTP Connection Manager Editor appears, then enter http://localhost:55300/GetEmployeesService.asmx
on Server URL field. HttpConnection is a specific HTTP endpoint on a website or a specific Web Services Description Language (WSDL) file on a website.
Click Test Connection to make sure that we connect successfully.
Open your Web Browser and enter the link http://localhost:55300/GetEmployeesService.asmx?wsdl
, then you will get the content in XML format. Copy the content and save it in .wsdl extention (D:\SSIS-Practice\SSIS_WebService\LoadEmployeeByWebAPI\Employee.wsdl
provides a standard XML-formatted list of available methods that can be called in the web services. It also provides information about what type of parameters can be used and what results the web service can returns.
: enter D:\SSIS-Practice\SSI
On Output tab
enter Service1. This is the Web Service name, we created before
enter GetEmployees. This is the Web Service method name
: we configure path of XML file which will used to be store data returned
Output Type: Variable
Drag and drop Data Flow Task, name it as Import Employees. Then double-click to open Data Flow tab
Drag and drop XML Source component then double-click to open XML Source Editor
On Connection Manager tab:
Data access mode
On Column tab
: XML data from variable. You can use other options such a XML file connection. In this case, you need to create a new file connection and point to Employee.xml file.
Un-check Use inline schema
Click on Generate XSD... button to generate a XSD file. We can understand XSD file as XML Schema Definition Language file so that SSIS can predict and validate data types.
If any error happens, open Employee.xml file and enter the XML content. The purpose is to help generate XSD file and SSIS will base on this file to predict data types.
<JobTitle>Production Technician - WC50</JobTitle>
: select all columns and ignore a warning window.
Drag and drop OLEDB Destination and connect to the table created before. On Mappings tab, I will do mappings for all columns.
Finally, I have the SSIS package as below
Step 4 - Execute the SSIS package
Verify the staging table to make sure that it is empty. Beside, you also ensure that the Web Service Application is running now.
Press F5 to execute
When working with Web Service Task and XML Task in SSIS, you must deal with XML data return by Web Services. It sometime returns data in complex XML format so XML Source could not read it. To solve, maybe you have to use XML Task or Script Task component to validate XML data before parsing and importing.