Can Microsoft Sql Server Call Soap Web Service

Download Web_Service_Task_Exp - 19.74 KB

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

Table of Content

Introduction
Information Source
Cease Betoken Creation
WSDL File Cosmos
Spider web Service Job configuration and consuming the spider web service using Web Service Job
Conclusion

Introduction

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

Xml -> Represent the data
Soap (Elementary Object Access Protocol) -> Data substitution
WSDL (Web Service Description Language) -> Describe the spider 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 Spider web Services chore 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 Spider 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 tabular array          if          information technology exists IF EXISTS (SELECT * FROM sys.objects WHERE proper name = N'          tbl_Players'          AND blazon =          '          U')     DROP Tabular array tbl_Players Go SET ANSI_NULLS ON GO --Create the tabular array CREATE Tabular array tbl_Players ( 	PlayerID INT IDENTITY, 	PlayerName VARCHAR(15), 	BelongsTo VARCHAR(15), 	MatchPlayed INT, 	RunsMade INT, 	WicketsTaken INT, 	FeePerMatch NUMERIC(sixteen,2) )  --Insert the records INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('          A. Won','          India',10,440,ten,          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','          Bharat',10,650,0,3600000) INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('          C. Barsat','          Republic of india',10,950,0,5000000) INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('          A. Mirza','          India',2,iii,38,          3600000)  INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('          K. Karol','          Us',fifteen,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('          Thousand. Loly','          United states',half-dozen,500,12,800000) INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('          S. Summer','          Usa',87,50,8,1230000) INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('          J.June','          U.s.a.',12,510,nine,          4988000)  INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('          A.Namaki','          Commonwealth of 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','          Commonwealth of australia',forty,66,0,1234) INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('          South. Benefaction','          Australia',170,888,ten,890) INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('          DC. Shane','          Commonwealth of 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,fifty,          22222) INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('          Yard. 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 beneath stored procedure created in our database whose script is as nether

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

(A) End Point Creation

Let us result the below script for cosmos 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]  	Land=STARTED 	Every bit HTTP  	( 		PATH=N'          /PlayerName'          , PORTS = (Articulate)  		,AUTHENTICATION = (INTEGRATED) 		, SITE=N'          localhost'          , CLEAR_PORT =          8000          ) 	FOR Lather  	( 		WEBMETHOD          '          PlayerList'          ( Proper name=N'          [SSISExperiments].[dbo].[usp_SelectPlayerRecords]') 		, BATCHES=DISABLED 		, WSDL=DEFAULT 		, DATABASE=North'          SSISExperiments'          , NAMESPACE=N'          http://SSISExperiments/Players'          ) GO

Code Explanation

The Equally HTTP section identifies the Path, Ports and hallmark methods.PATH ever starts with a forrard slash(/) and identifies the path under the root. In this case, we are creating an endpoint with a path of '/PlayerName' which is available on the local server. So the full path volition be http://localhost:8000/PlayerName.The PORTS is set to Clear indicates that we are using HTTP and the port lxxx.

The FOR Soap section identifies the spider web methods, WSDL and the Database.WEBMETHOD is use to identify the stored procedure(southward) 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, event the command

DROP ENDPOINT PlayerRecord_EP

and it will exist dropped

(B) WSDL File Creation

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

The partial output is given as under

We volition save the WSDL file in the hd. For that reason, in the IE browser, right click -> View Source ->File -> Salvage As…. Relieve the file every bit PlayerList.wsdl. at any convinient location on the difficult drive.

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

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

Stride 1: Permit us open BIDS and create a new Integration Services Project. In the Command Flow designer, elevate and drop a Web Service chore from the toolbox.

Stride 2: Double click on the Spider web Service Task for bringing upward the Web Service Chore Editor.

Pace 3: From the General list item, we will provide the data for

HttpConnection
WSDL file
OverwriteWSDLFile

(a) Configuring the HttpConnection field

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

Choose the "Server" tab from the Http Connexion Managing director Editor and let's do the post-obit settings

- Requite the Server URL (in our instance information technology is http://localhost:8000/PlayerName?wsdl)

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

Finally click OK

(b) Configuring the WSDL file field

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

It has to ready to TRUE

Subsequently this, let us click on Download WSDL button. If everything goes shine, we volition receive a successful download message

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

(i) Service

(ii) Corresponding Spider web method

Stride 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 push button

Pace 6: Build and Run the bundle.

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

which indicates that it is working.

Decision

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

Thank you for reading the article