Consuming a web service, importing the consumed xls file into a sql server database table

I believe that what you need to do is to add a web reference to your project, create an instance of the web service proxy class that is generated when you add a web reference, and then call the web method to get the file.

Thanks for your reply. When adding a web reference to the project, I have to enter the complete path to the Report Server Web service in the URL field. Can I add a url other than .asmx extension. In my case, the url I am provided has a .jsp extension. This shows that the web service is written in java. Following is the url (I have altered it for security reasons):

http://emat:7001/emat/common/JPOServlet.jsp?service=true&cmd=WS_ABO_CreateAccessoryReport&sk=xyz&tabs=WhereUsed&outputFileName=Abc_Report_WhereUsedTab_28Jan2010.xls

If a .jsp web service can be consumed in .Net, do I have to enter the complete path above as the url or only the path for the server and the web service without parameters, i.e:

http://emat:7001/emat/common/JPOServlet.jsp?

Please guide me here as I am a novice...

Thanks

Now, you are really diving into the deep end of the pool...it depends...

Does the Java web service use SOAP extensions, or JSON?  Is there anyone that can explain how this web service is configured?  It sounds like a REST web service.  .NET natively supports SOAP web services, but REST web services would require a different approach, such as using an HttpWebRequest.

Reference:

REST vs SOAP Web Services
http://www.petefreitag.com/item/431.cfm

The person in charge of the web service is out on vacation this week. I will try to find out the details sometimes next week. Thanks for the response.

I am still waiting to hear from the person in charge of the web service. I will let you know the details as soon as I get them. Just to keep you posted.

TheLearnedOne,

I have got the some info now. The web service is Rest based and when I try to add the web reference and enter the following url:

http://emat:7001/emat/common/JPOServlet.jsp?service=true&cmd=WS_ABO_CreateAccessoryReport&sk=xyz&tabs=WhereUsed&outputFileName=Abc_Report_WhereUsedTab_28Jan2010.xls

Instead of building the web reference file, I rather see a popup to open or save the data file as Excel file. This is the same behaviour I get when I paste the url in a browser. In both cases, it correctly opes and saves the Excel file with all the data.

Now, at this point, I want to know if there is a way for not seeing the popup when I try to add web reference to my project, rather having a web reference built which I can programmatically consume?

Here is the comments of the person:

 "I’ve never done any C# programming, so I’m not familiar with the APIs.  However, in Java, one would open a “URLConnection” and an associated InputStream to pull down the file. "

Your help is greatly appreciated.

Thanks

If you have simple requirements to connect to the URL, and get the file, you can use the System.Net.WebClient to do this:

WebClient client = new WebClient();
client.DownloadFile(url, localFileName);

Can you please elaborate. Are you saying that I do not have to add web reference to my project? Would I just use a simple aspx page and add the System.Net.WebClient in the Page_Load event?

I believe that is the first time that you have mentioned what type of application you are working with.  If you are working with an ASP.NET web site, then I would think that you wouldn't need a page.  You could create a new class, add a static DownloadFile(string url, string localFileName) method.

But I would have to call the class from some page, right?

I feel that I have too narrow a view (100 meter), when I really need a 10000 meter view.  Can you give me an overview of your process, please?

I am provided a web service url. When I run this url on a browser, I see a popup that asks about either opening or saving a Microsoft Excel file. When I select open or save option, it appropriately opens or saves the xls file on my hard drive. I want to consume this web service and import this xls file into a table in the SQL Server database.

In an attempt to consume the web service, I tried adding a web reference to my project within Visual Studio 2008. I entered the provided url in the URL field textbox present in the Report Server Web service. In my case, the url I am provided has a .jsp extension and the web service is written in java. The web service is Rest based, not SOAP based. Following is the url (I have altered it for security reasons):

http://emat:7001/emat/common/JPOServlet.jsp?service=true&cmd=WS_ABO_CreateAccessoryReport&sk=xyz&tabs=WhereUsed&outputFileName=Abc_Report_WhereUsedTab_28Jan2010.xls

Instead of building the web reference file, I rather saw a popup to open or save the data file as Excel file. This is the same behaviour I get when I paste the url in a browser. In both cases, it correctly opens and saves the Excel file with all the data.

All I want is to consume this rest based web service and get a handle on the input stream of data and then programmatically insert the data into SQL Server database.

From your last few conversations I got an impression that I do not have to consume a web service. Since it is a simple requirement to connect to the URL and get the file, I rather can use the System.Net.WebClient to do this, correct?

If the answer is yes, as I understand, I can write a class and call this download method from a simple aspx page, am I right?

OK, now we're getting somewhere...please expand on "Report Server Web service"...Crystal Reports, SSRS, etc...

I apologize, what I meant is the Add Web Reference Dialog box in the Visual Studio Solution Explorer (not sure why I put the wrong name of dialog box). In this dialog box I tried to enter the url I mentioned above...

Let me repeat the whole issue below with minor modification:

I am provided a web service url. When I run this url on a browser, I see a popup that asks about either opening or saving a Microsoft Excel file. When I select open or save option, it appropriately opens or saves the xls file on my hard drive. I want to consume this web service and import this xls file into a table in the SQL Server database.

In an attempt to consume the web service, I tried adding a web reference to my project within Visual Studio 2008. I entered the provided url in the URL field textbox present in the Add Web Reference Dialog Box. In my case, the url I am provided has a .jsp extension and the web service is written in java. The web service is Rest based, not SOAP based. Following is the url (I have altered it for security reasons):

http://emat:7001/emat/common/JPOServlet.jsp?service=true&cmd=WS_ABO_CreateAccessoryReport&sk=xyz&tabs=WhereUsed&outputFileName=Abc_Report_WhereUsedTab_28Jan2010.xls

Instead of building the web reference file, I rather saw a popup to open or save the data file as Excel file. This is the same behaviour I get when I paste the url in a browser. In both cases, it correctly opens and saves the Excel file with all the data.

All I want is to consume this rest based web service and get a handle on the input stream of data and then programmatically insert the data into SQL Server database.

From your last few conversations I got an impression that I do not have to consume a web service. Since it is a simple requirement to connect to the URL and get the file, I rather can use the System.Net.WebClient to do this, correct?

If the answer is yes, as I understand, I can write a class and call this download method from a simple aspx page, am I right?

Let's go through this:

1) You can't add a web reference using the dialog with the REST web service, since it is URL-based, and not WSDL-based.

2) You need to download the file with something like an HttpWebRequest (complex requirement), or WebClient (simple requirement).  The WebClient is a simple wrapper for an HttpWebRequest.

3) The trigger for call to the class method is what I am not seeing.  Are you needing a hyperlink on a web page, that will download the file, and then upload to SQL Server?  If it is something else, please indicate.

I am not sure if it can be done or not, but if it can be, ideally I want that class to be called from a DTS or SSRS on nightly bases. It should download data from the url and insert into an SQL Server database. I want to minimize any human interaction as much as I can.

Thanks a lot TheLearnedOne. I think you deserve all the points however I need further help on this. Should I open a new question and redirect you to that link. I made sure that I have the SSIS installed and accessible. Now I need a walk through on how to go to HTTP Connection Manager Editor on SQL Server 2005, how to create, debug and execute the task etc. Please suggest!

I have opened a new question. Please help if you can. Here is the link:

https://www.experts-exchange.com/questions/25123641/Need-a-SSIS-walk-through-help-on-downloading-an-Excel-file-using-a-url.html?fromWizard=true


Thanks