Click here to Skip to main content
15,868,141 members
Articles / Database Development / SQL Server / SQL Server 2008

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

Rate me:
Please Sign up or sign in to vote.
4.75/5 (12 votes)
19 May 2011CPOL4 min read 74.4K   685   22   8
In this article we will see how we can consume a web service expose through HTTP Endpoint via Web Service Task of SSIS

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

1.jpg

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

DROP ENDPOINT PlayerRecord_EP
and it will be dropped

(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

2.jpg

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.

3.jpg

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…>

4.jpg

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)

5.jpg

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

6.jpg

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

(i) Service

(ii) Corresponding Web method

7.jpg

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

8.jpg

Finally, click on the OK button

Step 6: Build and Run the package.

9.jpg

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

10.jpg

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Comments and Discussions

 
Questionconfused Pin
amine2503-May-15 4:02
amine2503-May-15 4:02 
QuestionThe request failed with HTTP status 401: Unauthorized Pin
ARJUN191023-Jul-13 8:52
ARJUN191023-Jul-13 8:52 
QuestionPosting to a Web Service with Endpoints? Pin
Member 390335020-Feb-13 7:54
Member 390335020-Feb-13 7:54 
Is it possible, with either SSIS or TSQL, to post records to a web service using endpoints?
GeneralMy vote of 2 Pin
nrutter18-May-11 4:27
nrutter18-May-11 4:27 
GeneralRe: My vote of 2 Pin
Niladri_Biswas19-May-11 1:06
Niladri_Biswas19-May-11 1:06 
GeneralRe: My vote of 2 Pin
ThePotty17-Aug-13 0:41
ThePotty17-Aug-13 0:41 
GeneralMy vote of 5 Pin
Deepak.Kumar.BD14-May-11 18:52
Deepak.Kumar.BD14-May-11 18:52 
GeneralMy Vote is 5 Pin
Bilaltm12-May-11 18:01
Bilaltm12-May-11 18:01 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.