Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

Get SQL to call a Web Service with secure connection

connection

  • Please log in to reply
5 replies to this topic

#1 PSmith

PSmith

    CC Lurker

  • Just Joined
  • Pip
  • 5 posts

Posted 25 August 2011 - 05:11 AM

Hey there I am a Junior Developer and i need SQL to call a web service but it has to be a secure connection as i work with valuable information. Now the Web service I want my query to use is our Competitions, so they must not be able to access our data like our DB's we just want to use that to send out weekly reports. Now i want to know if its possible as I can not change code to the web service, Would it be a waste of time should i just rather create a new app and use a Console app to just trigger SQL once a week then in that console app i take the data convert to a excel sheet and email the data to the clients? I apoligise for my lack of info i am giving you as my knowledge is not that great but I am trying my best to get up to standard as i just came out of college a week ago
  • 0

#2 sam_coder

sam_coder

    CC Addict

  • Senior Member
  • PipPipPipPipPip
  • 380 posts

Posted 25 August 2011 - 09:15 AM

You're right, its a pretty vague question. =)

But that doesn't mean I won't be helpful.

Depending on the Database Platform your using, whether or not that is possible really depends. I've never even attempted such a thing to be honest. But I do know that in SQL server, you can run create .NET modules that are exposed as functions or procedures to your database.

For example, it might be possible to do something like this

SELECT readFromService(@parameter1, @parameter2)

The underlying code of course could do almost anything you put the effort into writing.

I have to wonder if perhaps you're looking at the problem backward though.

I would almost set up an ETL that runs periodically, to securely extract information from that web service, and drop it into a local database, so that you could execute more natural queries against it.

I'm taking both sanity and performance into consideration here. =)

There are a great many ways you could do this actually. In fact I'm almost certain that SQL Server has a way of pulling data in an ETL job like fashion from a soap service. And if it didn't, yes, you could write even a simple console application that does the job, and just get's run periodically.
  • 0

#3 PSmith

PSmith

    CC Lurker

  • Just Joined
  • Pip
  • 5 posts

Posted 26 August 2011 - 01:18 AM

I spoke to one of our DB's and i have a more clear view of whats going on,

We using SQL 2008 R2 There is a query that runs once a month (going to change to weekly) that puts the data in a SSIS package.

But we dont know how to use SSIS to get the data from the Web Service via a https: connection(Secure) so i need to manually write a program to extract that data from external sources
(The Web Service from the competition and one of our DB's)

The Web service gives me a flat xml file and the DB's is a procedure that i can just put in a data set and my output must be a .csv file that needs to be generated once a week and shipped via email to clients.

Now they told me about this Web Service that is running and said i need to make use of that so either I Call the Web service from SQL or create a app to call the web service and extract that data.

So i guess it would be just easier to create a app that i know would work than try to use the Web Service that might fail and put me behind schedule.

Unless there are other more efficient approaches.

So lets sum this up: I want to know how to use SSIS(SQL 2008) to get data from Web Service via secure connection(https:) I have the certificates for the web service

"Intel inside Idiot Outside" =D

---------- Post added at 09:23 AM ---------- Previous post was at 07:46 AM ----------

And i just realize you did answer me tho lol, I am going to try and create a .Net module to extract the information from the Web Service.

And store in our local DB's so then SSIS can do the rest :)

---------- Post added at 11:18 AM ---------- Previous post was at 09:23 AM ----------

Will this be what the code looks like in my module to fetch the data from the Web Service? "SELECT readFromService(@parameter1, @parameter2)"

Edited by PSmith, 25 August 2011 - 11:06 PM.

  • 0

#4 PSmith

PSmith

    CC Lurker

  • Just Joined
  • Pip
  • 5 posts

Posted 26 August 2011 - 01:27 AM

This is just silly i posted a Reply that explained my situation completely and it was removed after it was here for 2 hours.

But allas i know what i want now, I want to create a module in SQL to get data from a Web Service, The data on that service is send via a flat xml file

I want to store that data in a local DB as you said then I can use SSIS for ETL. This all looks good on paper it saves the effort of going and creating a console app

To read the data from the Web Service. Can this be done and how?

Useful Info: Using SQL 2008 R2
Needs to be a secure connection https:
I have certificates to the Web Service


If you need more info pls let me know, I have never worked with a module before so tonight i will go threw some tutorials
  • 0

#5 sam_coder

sam_coder

    CC Addict

  • Senior Member
  • PipPipPipPipPip
  • 380 posts

Posted 26 August 2011 - 06:21 AM

well, it really depends on the type of service that you're trying to consume. This is really just as simple as making an HTTP request, posting your soap question (XML), and downloading/parsing the soap response (also XML)

I'm not 100% certain the facilities offered when hosting CLR modules from within SQL server. But this would most easily be done by specifying the service as either a web or a service reference. doing so will generate all the client code for you.

typically, when you create your project, you can right click the references node in the project explorer, and select say.. add service reference. You would then hand in the URL to the WSDL location. This is typically http://address/of/service.svc?wsdl (or service.asmx?wsdl)

you then specify a namespace for it, eg, ServiceReference

once this is done, assuming all is A-OK, you can call the service like this...

using (ServiceReference.ServiceClient clientProxy = new ServiceReference.ServiceClient()) {
     return clientProxy.MethodIWantToCall("any", "arguments");
}

I'd give a more specific example, but I'm away from hope, typing this on my netbook, which has neither windows, nor visual studio.


That said, if you wanted to take a more direct approach (something I wouldn't bother, if you have the ability to do something like I just mentioned), there are lots of ways to do this more manually in the framework.

For example, you could use the WebClient in the System.Net namespace, to create your request, and then parse the response through any XML means. Like an XmlDocument.


Anyways, I'll post this, see what your response is, I should be back at my workstation pretty soon.
  • 0

#6 sam_coder

sam_coder

    CC Addict

  • Senior Member
  • PipPipPipPipPip
  • 380 posts

Posted 27 August 2011 - 06:52 AM

I spoke to one of our DB's and i have a more clear view of whats going on,

We using SQL 2008 R2 There is a query that runs once a month (going to change to weekly) that puts the data in a SSIS package.

But we dont know how to use SSIS to get the data from the Web Service via a https: connection(Secure) so i need to manually write a program to extract that data from external sources
(The Web Service from the competition and one of our DB's)

The Web service gives me a flat xml file and the DB's is a procedure that i can just put in a data set and my output must be a .csv file that needs to be generated once a week and shipped via email to clients.

Now they told me about this Web Service that is running and said i need to make use of that so either I Call the Web service from SQL or create a app to call the web service and extract that data.

So i guess it would be just easier to create a app that i know would work than try to use the Web Service that might fail and put me behind schedule.

Unless there are other more efficient approaches.

So lets sum this up: I want to know how to use SSIS(SQL 2008) to get data from Web Service via secure connection(https:) I have the certificates for the web service

"Intel inside Idiot Outside" =D

---------- Post added at 09:23 AM ---------- Previous post was at 07:46 AM ----------

And i just realize you did answer me tho lol, I am going to try and create a .Net module to extract the information from the Web Service.

And store in our local DB's so then SSIS can do the rest :)

---------- Post added at 11:18 AM ---------- Previous post was at 09:23 AM ----------

Will this be what the code looks like in my module to fetch the data from the Web Service? "SELECT readFromService(@parameter1, @parameter2)"



Hey , sorry, this post for some reason never showed up yesterday, I'll take a read through as soon as I drop my wife off to work. =)
  • 0





Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download