August 28, 2018 at 9:18 am
Any help would be much appreciated. I need to query a website for a number (Sales Figure) and insert it into a table. How do I go about doing this? I know I will have to contact the CLoud provider of the software (BPMonline) to get some sort of address and probably import from an xml file but I have no idea where to even start.
Thanks
August 28, 2018 at 9:27 am
So there needs to be a lot more information provided. Do you mean they have some kind of a webservice to provide the information? If so you'll need to get the specs for that service from them and then decide the best method to approach calling it. For example is this something that's going to done on demand in real time? are you using it for processing in batch?
August 28, 2018 at 9:33 am
The Sales people put in figures and then it sums that figure along with probability. It is just 2 numbers that they calculate that I need to pull from that website to put into a table daily.
August 28, 2018 at 10:06 am
bswhipp - Tuesday, August 28, 2018 9:33 AMThe Sales people put in figures and then it sums that figure along with probability. It is just 2 numbers that they calculate that I need to pull from that website to put into a table daily.
This sounds like a job for a Script Task within SSIS as opposed to something you could do with T-SQL. You may or may not be able to use a webservice call to get the data, and if not, you may need to use a "screen scrape" methodology. The latter is going to require procedural language knowledge (C# or VB) in order to perform, and making a webservice call could go either way within SSIS.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 28, 2018 at 10:44 am
I do have knowledge of vb but never did a webservice call
August 28, 2018 at 11:24 am
bswhipp - Tuesday, August 28, 2018 10:44 AMI do have knowledge of vb but never did a webservice call
And doing one presumes that whatever website you are referring to even has one available for that purpose, and that it's documented as to how to call it and what to expect from the response. Often, such responses are XML. But first you'd need to know that such exists. Otherwise, you're only option is either "screen scrape", or get remote access to the database that feeds that website via a Linked Server.
If you end up doing a screen scrape, you are going to be dependent on the structure of that html web page. If that structure changes, you might well discover that such changes will break your program, so be aware that such things as screen scrapes are not what you call "set it and forget it".
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 28, 2018 at 12:30 pm
There is a Web Service task in SSIS which may make things easier.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 28, 2018 at 12:30 pm
I am in contact with them but they are slow to reply and sometimes are not the best support. At least I am learning something about it. I had to think about your screen scrape comment. I didn't understand it. I understand it now as downloading the page and then searching ion the page for the content? I would like to know how to do that although it would not probably happen here since there is a logon and password to get to the data.
August 28, 2018 at 12:36 pm
bswhipp - Tuesday, August 28, 2018 12:30 PMI am in contact with them but they are slow to reply and sometimes are not the best support. At least I am learning something about it. I had to think about your screen scrape comment. I didn't understand it. I understand it now as downloading the page and then searching ion the page for the content? I would like to know how to do that although it would not probably happen here since there is a logon and password to get to the data.
It's possible to 'stream' the HTML returned from a particular URL into a string variable and then to analyse the text in that variable.
For example, if you know that the piece of data you are interested in always appears after the text "Here is some unique placeholder text", you can use standard string functions to grab that data. This is one kind of 'screen-scraping'.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 28, 2018 at 12:43 pm
bswhipp - Tuesday, August 28, 2018 12:30 PMI am in contact with them but they are slow to reply and sometimes are not the best support. At least I am learning something about it. I had to think about your screen scrape comment. I didn't understand it. I understand it now as downloading the page and then searching ion the page for the content? I would like to know how to do that although it would not probably happen here since there is a logon and password to get to the data.
This is going to be a very very simple explanation of web services, but basically they're exactly the same thing you do when you're using your browser. When you connect to a web site or service you send a method in the call to the website, the basic ones are GET which is what you do when you type say google.com into your browser and POST which is what happens when you say enter some form information like username/password into a webpage and hit submit. The body of a request or response is the payload, this could be the webpage you get back from one of the above calls or the data you are submitting for a POST. You also need to know the end point which is the web address you're connecting to.
That's the very basics, there are different standards for services designed to be human usable vs consumed by a machine but the basic technology is the same. If you're indicating that they have a username/password page that seems to be the human use able type of page. What you need to do is find out if there's another way they provide to get the data that designed for some kind of web service like SOAP, REST or some home cooked whatever for consumption by a computer.
August 28, 2018 at 12:46 pm
bswhipp - Tuesday, August 28, 2018 12:30 PMI am in contact with them but they are slow to reply and sometimes are not the best support. At least I am learning something about it. I had to think about your screen scrape comment. I didn't understand it. I understand it now as downloading the page and then searching ion the page for the content? I would like to know how to do that although it would not probably happen here since there is a logon and password to get to the data.
Not necessarily. It's possible to script the logon process as an action, but it sounds like you may not have the knowledge necessary to know how to manipulate the objects correctly for this purpose, and again, very small changes in the website can completely muck up your programming effort.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 28, 2018 at 1:20 pm
ZZartin - Tuesday, August 28, 2018 12:43 PMbswhipp - Tuesday, August 28, 2018 12:30 PMI am in contact with them but they are slow to reply and sometimes are not the best support. At least I am learning something about it. I had to think about your screen scrape comment. I didn't understand it. I understand it now as downloading the page and then searching ion the page for the content? I would like to know how to do that although it would not probably happen here since there is a logon and password to get to the data.This is going to be a very very simple explanation of web services, but basically they're exactly the same thing you do when you're using your browser. When you connect to a web site or service you send a method in the call to the website, the basic ones are GET which is what you do when you type say google.com into your browser and POST which is what happens when you say enter some form information like username/password into a webpage and hit submit. The body of a request or response is the payload, this could be the webpage you get back from one of the above calls or the data you are submitting for a POST. You also need to know the end point which is the web address you're connecting to.
That's the very basics, there are different standards for services designed to be human usable vs consumed by a machine but the basic technology is the same. If you're indicating that they have a username/password page that seems to be the human use able type of page. What you need to do is find out if there's another way they provide to get the data that designed for some kind of web service like SOAP, REST or some home cooked whatever for consumption by a computer.
I am somewhat familiar with get and post but I have never done it in code and wouldn't know where to start. I appreciate all of everyones' input and will try to work on this tomorrow since its time for me to hit the gym.
August 28, 2018 at 1:28 pm
bswhipp - Tuesday, August 28, 2018 1:20 PMI am somewhat familiar with get and post but I have never done it in code and wouldn't know where to start. I appreciate all of everyones' input and will try to work on this tomorrow since its time for me to hit the gym.
Correct priorities, bravo 🙂
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 5, 2018 at 8:22 am
Well they are of no help. They can't even understand what I want. I think they are Russian.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply