Problem

I need to retrieve data from my partner system but they do not allow to access the system directly. Instead they provide me some Web Services to retrieve data what I need.

Solution

One of approaches is we use Web Service Task and XML Task components in SSIS. In this article, I’m going to demonstrate how we use these components to retrieve data from Web Services.

Requirement

I need to retrieve list of employees from my partner system and they provide me a Web Service name GetEmployees. For demonstration purpose, I will create a Web Service at my local, it is as same as my partner Web service.

Step 1:

Create a project Web Service Application and create a web method GetEmployees. This returns list of employees. After building it, URL of Web Service method is located at http://localhost:55300/GetEmployeesService.asmx

[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=HSSSC1PCL01198\SQLSERVER2014;Initial Catalog=AdventureWorks2014;Integrated Security=SSPI;";
 string stQueryText = "SELECT NationalIDNumber,[LoginID],JobTitle,BirthDate,[MaritalStatus],[Gender],[HireDate] FROM HumanResources.Employee WHERE NationalIDNumber In(10708100) 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
 {
 // return dt;
 
 }
 
 XmlDocument xmldoc = new XmlDocument();
 xmldoc.LoadXml(xml);
 return xmldoc;
 }

Step 2:

  • Create a new SSIS package LoadEmployee_WebService.dtsx. Then drag and drop Web Service Task component to Control Flow tab.
  • Create a variable EmployeesOutputConnectionFile as XML out-put file connection. This file stores the result which my web service returns (list of employees in XML format)
  • Double click on Web Service Task. Then Web Service Task Editor window appears.
  • In General tab: I create new HttpConnection to point to my Web Service method which I created before. HttpConnection is a specific HTTP endpoint on a website or a specific Web Services Description Language  (WSDL) file on a website. Must test connection to be sure this Web Service is working.HttpConnection
  • 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.
  • In this example, I try to create .wsdl file manually by enter http://localhost:55300/GetEmployeesService.asmx?wsdl . It generates a standard XML-formatted. Save it as .wsdl file and store it at my local laptop (D:\SSIS-Practice\WebServiceApp\EmployeeListServices\EmployeeListServices\Employees.wsdl) . Then enter the above path of file into WSDLFile.
  • In Input tab: select Service and Method, which my web service providesWeb Service Editor - Input tab
  • In Output tab: configure to allow to save the result to XML file at my localWeb Service Editor - Output
  • Click OK

Step 3:

  • Drag and drop Data Flow control into the package
  • On Data Flow tab, drag and drop XML Source component as my source, OLE DB Destination component as my destination
  • Double click on XML Source
  • In Connection Manager tab, select Data access mode is XML file from variable, and then select the variable [User::EmployeesOutputConnectionFile].  Un-check on Use inline schema check-box. Click on Generate XSD button to generate .xds file, this file is XML Schema Definition Language so that SSIS can predict and validate data types and lengths.XMLSource
  • In Columns tab, select input column to insert data into my database. Select all by default. At this step, a warning window can be appeared, click OK to continue.

Step 4

Create a table Employee

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
GO

 

Step 5

Map columns between XML Source and OLE DB Destination

DataFlow_XMLSource_Employee_WS

Click OK

Step 6

Run package

FinalResult

CONCLUSION

Web Service Task and XML Task are useful components when we want to retrieve data from any Web Services within XML format. However, Web Service Task can retrieve only the results from a web service call. We may find that we must prepare, extract and validate our XML files before running them through ETL process – XML Task.

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s