How to load data from Web Service APIs

Dung DinhData Platform Consultant
CERTIFIED EXPERT
Published:
Updated:
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.


Scenario

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.


Development environment


Visual Studio 2010 (VS2010)
SQL Server 2014 Developer
SQL Server Data Tools for Visual Studio 2013
AdventureWorks2014 database



Step 1 - Create a staging table


USE Demo
                      GO
                      IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.[Employee_WS]') AND type in (N'U'))
                      BEGIN
                      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]
                      END

Open in new window

 

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.

Open GetEmployeeServices.asmx and create a new web service method by following code. The method returns list of employees in HumanResources.Employee table in AdventureWorks2014 database. You must edit stConnectionOLEDB to meet your environment such as database server, database name, authentication mode,...

[WebMethod]
                              public XmlDocument GetEmployees()
                              {
                                  var xml = "<?xml version=\"1.0\" encoding=\"utf-16\" ?>\n";
                      
                                  try
                                  {
                                      //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))
                                      {
                                          sqlcon.Open();
                                          SqlCommand cmd = new SqlCommand(stQueryText, sqlcon);
                                          SqlDataReader reader = cmd.ExecuteReader();
                                          while (reader.Read())
                                          {
                                              xml = reader[0].ToString();
                                          }
                                          sqlcon.Close();
                                      }
                                  }
                                  catch
                                  {
                                      
                                  }
                      
                                  XmlDocument xmldoc = new XmlDocument();
                                  xmldoc.LoadXml(xml);
                                  return xmldoc;
                              }

Open in new window


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 ServiceXMLEmployeeFile.png


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 General:

HttpConnection: 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.
HttpConnection.PNG
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)
WSDL file 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.
 WSDLEmployee.pngWSDLFile: enter D:\SSIS-Practice\SSIS_WebService\LoadEmployeeByWebAPI\Employee.wsdl
WSDLFile.PNG


On Input tab:

Service: enter Service1. This is the Web Service name, we created before
Method: enter GetEmployees. This is the Web Service method name
Input.PNG


On Output tab: we configure path of XML file which will used to be store data returned

Output Type: Variable
Variabl​e: User::XMLEmployeeFile
Output.PNG
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: 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.
Variable name: User::XMLEmployeeFile
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.
XMLSource.PNGIf 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.
<?xml version="1.0"?>
                      <Employees xmlns="">
                        <Employee>
                          <NationalIDNumber>10708100</NationalIDNumber>
                          <LoginID>adventure-works\frank1</LoginID>
                          <JobTitle>Production Technician - WC50</JobTitle>
                          <BirthDate>1971-07-24</BirthDate>
                          <MaritalStatus>S</MaritalStatus>
                          <Gender>M</Gender>
                          <HireDate>2009-02-23</HireDate>
                        </Employee>
                      </Employees>

Open in new window



 
On Column tab: 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.

OLEDBConnection.PNG
Finally, I have the SSIS package as below
SSIS_Package.png


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
ExecuteSSIS.png

Conclusions

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.
4
15,893 Views
Dung DinhData Platform Consultant
CERTIFIED EXPERT

Comments (1)

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Very well illustrated, and reads real well considering how difficult the subject is.  Voting Yes.

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.