Import and Export Wizard: Invalid Internet Address when importing Excel from a web page

Question

Import and Export Wizard: Invalid Internet Address when importing Excel from a web page

Karen Grube

SSC Eights!

Points: 994
More actions
January 29, 2013 at 9:04 am

#394055

I'm running SQL Server 2008 R2 on a Windows 2008 R2 server.

http:\\www.eia.gov\forecasts\steo\xls\STEO_m.xls
I can open this file in a browser window, and even from within Excel. It just opens in Excel automatically. I'm trying to import this file into a SQL database using the 32-bit Import and Export wizard and the Excel data source, but I keep getting the following error message.
TITLE: SQL Server Import and Export Wizard
------------------------------
The operation could not be completed.
------------------------------
ADDITIONAL INFORMATION:
Invalid internet address. (Microsoft Access Database Engine)
------------------------------
Any suggestions as to what the problem might be and how to resolve it? I'm submitting this in the Integration Services area because I will be using the package that gets created by this process within SSIS.
Thanks!
Karen

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

cafescott SSCarpal Tunnel Points: 4724 More actions · Answer 1

Karen,

You could try downloading the spreadsheet to a location that your SQL Server can see. Then run the import/export wizard on the file you downloaded.

Karen Grube SSC Eights! Points: 994 More actions · Answer 2

Thank you. That would probably work, but I don't know how to automate downloading this particular file. If you could give me some guidance in that regard, that would be great. Still, my question has more to do with why I'm getting this error and what to do about it. Seriously, try opening the file directly from a web browser and see how easy it is. Then, try opening it from the Import Export wizard and you'll see the problem I'm having. Please let me know what you discover and if you have any solutions.

Thanks!

Karen

cafescott SSCarpal Tunnel Points: 4724 More actions · Answer 3

Well, that certainly is a complex spreadsheet. You could see if you can download the spreadsheet using a script task. (I don't know how it can be done, but it seems a script task can do it if it is at all possible.)

I would then create an Excel connection manager for each worksheet you needed the data for. For each connection manager, define a range to try to filter out all the stuff you don't need. Then try to get the data into tables.

I haven't worked with an Excel file this complicated. Hopefully some other people here can help with it.

Karen Grube SSC Eights! Points: 994 More actions · Answer 4

If someone can provide a sample script task that will download that file, that would be terrific! Yes, the spreadsheet is complex. I can deal with the layout. I also have a call in to the EIA to see if there is a data file rather than a spreadsheet. We'll see!

cafescott SSCarpal Tunnel Points: 4724 More actions · Answer 5

Karen, Google is our friend. I just did a quick search and found these pages:

http://stackoverflow.com/questions/10078745/what-ssis-task-can-be-used-to-download-files-from-an-https-url

http://microsoft-ssis.blogspot.pt/2011/05/download-source-file-from-website-with.html

Karen Grube SSC Eights! Points: 994 More actions · Answer 6

Thank you!!! These are perfect for what I needed. Thanks so much.