Access a website to get a CSV file to be loaded into SQL

Question

Access a website to get a CSV file to be loaded into SQL

jcollins

SSC-Addicted

Points: 481
More actions
April 10, 2007 at 11:19 am

#171970

Does anyone know of a way to access a website for a CSV file to be loaded into SQL Server 2000 within a DTS package?

jcollins

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

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

David McFarland SSChampion Points: 11815 More actions · Answer 1

You could use the File Transfer Protocol task for FTP, and since you have an Execute Process Task available, you can do it in just about any manner you want, such as command line utilities, etc. You could also likely do it with ActiveX Script Task, but that seems like a lot of work.

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 2

Can you give some more information as to how the CSV file is manually downloaded from the website?

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

jcollins SSC-Addicted Points: 481 More actions · Answer 3

This is the link to the CSV that I need loaded in to a table located in SQL Server 2000.

http://www.globalindices.standardandpoors.com/data/wdpmi_bus.csv

Thanks,

Jeff

jcollins

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 4

Try this ActiveX script in a DTS package:

Function Main()

Dim objSvrHTTP

Dim fso

Dim f

Set objSvrHTTP = CreateObject("Msxml2.ServerXMLHTTP.4.0")

objSvrHTTP.open "GET", "http://www.globalindices.standardandpoors.com/data/wdpmi_bus.csv", false

objSvrHTTP.send

msgbox objSvrHTTP.responseText

Set fso = CreateObject("Scripting.FileSystemObject")

Set f = fso.CreateTextFile("c:\download.csv", True)

f.write objSvrHTTP.responseText

Set f = Nothing

Set fso = Nothing

Main = DTSTaskExecResult_Success

End Function

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

jcollins SSC-Addicted Points: 481 More actions · Answer 5

Adrian

That worked, and is exactly what I needed.

Thanks,

Jeff

jcollins