Consuming a Web Service created through Sql Server HTTP Endpoints via Web Service task in SSIS

• Download Web_Service_Task_Exp - 19.74 KB

Consuming a Web Service created through Sql Server HTTP Endpoints via Web Service task in SSIS

Table of Content

1. Introduction
2. Data Source
3. End Point Creation
4. WSDL File Creation
5. Web Service Task configuration and consuming the web service using Web Service Task
6. Conclusion

Introduction

Web services allow various applications to communicate with each other. They are based on certain standards

• Xml -> Represent the data
• SOAP (Simple Object Access Protocol) -> Data exchange
• WSDL (Web Service Description Language) -> Describe the web service capabilities.

A HTTP Endpoint is a Sql Server object which is use by Sql Server to communicate over the network. It includes the web method(s) which are typically the Stored Procedures (T-Sql or CLR) executed within the database and are queried by the web services.

The Web Services task in SSIS is use for executing web service method(s).

In this article we will examine how we can consume the web service method exposed through the Http Endpoint through SSIS Web Service task.

Data Source

For this experiment, we will use the below script to generate and populate the Players table which is named as (tbl_Players)

-- Drop the table if it exists
IF EXISTS (SELECT * FROM sys.objects WHERE name = N'tbl_Players' AND type = 'U')
    DROP TABLE tbl_Players
GO
SET ANSI_NULLS ON
GO
--Create the table
CREATE TABLE tbl_Players (
	PlayerID INT IDENTITY,
	PlayerName VARCHAR(15),
	BelongsTo VARCHAR(15),
	MatchPlayed INT,
	RunsMade INT,
	WicketsTaken INT,
	FeePerMatch NUMERIC(16,2)
)

--Insert the records
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A. Won','India',10,440,10, 1000000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A. Cricket','India',10,50,17, 400000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('B. Dhanman','India',10,650,0,3600000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('C. Barsat','India',10,950,0,5000000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A. Mirza','India',2,3,38, 3600000)

INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('M. Karol','US',15,44,4, 2000000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('Z. Hamsa','US',3,580,0, 400)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('K. Loly','US',6,500,12,800000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Summer','US',87,50,8,1230000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('J.June','US',12,510,9, 4988000)

INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A.Namaki','Australia',1,4,180, 999999)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('Z. Samaki','Australia',2,6,147, 888888)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('MS. Kaki','Australia',40,66,0,1234)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Boon','Australia',170,888,10,890)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('DC. Shane','Australia',28,39,338, 4444499)

INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Noami','Singapore',165,484,45, 5678)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('Z. Biswas','Singapore',73,51,50, 22222)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('K. Dolly','Singapore',65,59,1,99999)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Winter','Singapore',7,50,8,12)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('J.August','Singapore',9,99,98, 890)

We will also have the below stored procedure created in our database whose script is as under

If Exists (Select * from sys.objects where name = 'usp_SelectPlayerRecords' and type = 'P')
    Drop Procedure usp_SelectPlayerRecords
Go
-- Create the  stored procedure
Create Procedure [dbo].[usp_SelectPlayerRecords]
As
Begin
	Select 
		PlayerID
		,PlayerName
		, BelongsTo
		, MatchPlayed
		,RunsMade
		,WicketsTaken
		,FeePerMatch
	From
	tbl_Players
End

(A)  End Point Creation

Let us issue the below script for creation of the endpoint

IF EXISTS ( SELECT NAME FROM sys.http_endpoints WHERE NAME = N'PlayerRecord_EP' )
DROP ENDPOINT PlayerRecord_EP
GO
CREATE ENDPOINT [PlayerRecord_EP] 
	STATE=STARTED
	AS HTTP 
	(
		PATH=N'/PlayerName'
		, PORTS = (CLEAR) 
		,AUTHENTICATION = (INTEGRATED)
		, SITE=N'localhost'
		, CLEAR_PORT = 8000
	)
	FOR SOAP 
	(
		WEBMETHOD 'PlayerList'
		( NAME=N'[SSISExperiments].[dbo].[usp_SelectPlayerRecords]')
		, BATCHES=DISABLED
		, WSDL=DEFAULT
		, DATABASE=N'SSISExperiments'
		, NAMESPACE=N'http://SSISExperiments/Players'
	)
GO

Code Explanation

The AS HTTP section identifies the Path, Ports and authentication methods.PATH always starts with a forward slash(/) and identifies the path under the root. In this example, we are creating an endpoint with a path of '/PlayerName' which is available on the local server. So the full path will be http://localhost:8000/PlayerName.The PORTS is set to CLEAR indicates that we are using HTTP and the port 80.

The FOR SOAP section identifies the web methods, WSDL and the Database.WEBMETHOD is use to identify the stored procedure(s) that are called through the endpoint.

Running the script will create the endpoint in the Server Objects

N.B.~ If we want to drop the endpoint, issue the command

 DROP ENDPOINT PlayerRecord_EP 

(B)  WSDL File Creation

In our case,the WSDL can be viewed at the following location http://localhost:8000/PlayerName?wsdl

The partial output is given as under

We will save the WSDL file in the hard disk. For that reason, in the IE browser, right click -> View Source ->File -> Save As…. Save the file as PlayerList.wsdl. at any convinient location on the hard drive.

(C)  Web Service Task configuration and consuming the web service using Web Service Task

We will follow the below steps in order to satisfy the requirement

Step 1: Let us open BIDS and create a new Integration Services Project. In the Control Flow designer, drag and drop a Web Service task from the toolbox.

Step 2: Double click on the Web Service Task for bringing up the Web Service Task Editor.

Step 3: From the General list item, we will provide the information for

1. HttpConnection
2. WSDL file
3. OverwriteWSDLFile

(a) Configuring the HttpConnection field

In the HttpConnection, let's click on the empty area and from the dropdown list let us choose <New Connection…>

Choose the "Server" tab from the Http Connection Manager Editor and let's do the following settings

- Give the Server URL (in our case it is http://localhost:8000/PlayerName?wsdl)

- Enter the credentials i.e. User Name, Password, Domain ( this is optional though)

Finally click OK

(b) Configuring the WSDL file field

In the WSDL File field, let's pick up the PlayerList.wsdl file from the location it was saved.

(c) Configuring the OverwriteWSDLFile field

It has to set to TRUE

After this, let us click on Download WSDL button. If everything goes smooth, we will receive a successful download message

Step 4: In the input screen available from the Input tab, let us enter the information for the

(i) Service

(ii) Corresponding Web method

Step 5: In the output screen available from the Output tab, we will set OutputType to file connection and then specify the file name.

Finally, click on the OK button

Step 6: Build and Run the package.

Now open the Outputfile.txt and the content (partial) is as under

which indicates that it is working.

Conclusion

In this article we have seen how to create a HTTP Endpoint,configuring the Web Service Task and finally consuming the web service via the Web Service task. The consumption of the web service can also be done using script component about which we can discuss in some other article. Hope this helps

Thanks for reading the article