Download File Via DTS

Question

Download File Via DTS

sql_Reporter

SSCrazy

Points: 2329
More actions
July 28, 2006 at 2:28 am

#169431

Hi i have a web link that bring me to a web page, I then select a link on the page which in turn down loads a database for me, to my network drive, i would like to automate this in my DTS package but i do not know how, any help/ideas would be great.

Viewing 11 posts - 1 through 10 (of 10 total)

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

workingdba SSC Veteran Points: 220 More actions · Answer 1

That's an interesting requirement. Does the URL change for the starting link? Does the URL change for the database link? Or always a static address?

cliffb SSCarpal Tunnel Points: 4597 More actions · Answer 2

You can use the MSXML.XMLHTTP object in an activexscript to read the contents of a location to an adodb.stream. You can then save the stream to a file.

Dim HTTP

Dim Stream

Const adTypeBinary = 1

Const adSaveCreateNotExist = 1

Const adSaveCreateOverWrite = 2

strSource = "http://myURL"

strDest = "SaveFileAs"

Set Stream = CreateObject("adodb.stream")

Set HTTP = WScript.CreateObject("Microsoft.XMLHTTP")

HTTP.open "GET", strSource, False

HTTP.send

Stream.type = adTypeBinary

Stream.open

Stream.write HTTP.responseBody

Stream.savetofile strDest, adSaveCreateOverWrite

Set Stream = nothing

Set HTTP = nothing

sql_Reporter SSCrazy Points: 2329 More actions · Answer 3

The URL is static and its a access database that i down load. i will try the code and let you know how i get on, am using SQL 2000, i hope it will work. thanks

sql_Reporter SSCrazy Points: 2329 More actions · Answer 4

Hi am having a runtime problem with the code you gave me, am getting a error on the WScript , it say object required: WScript , code 0 .

workingdba SSC Veteran Points: 220 More actions · Answer 5

I was able to get that script working as a test. I saved it to a txt file then renamed to script.vbs. Didn't do this as a wscript.

You have to modify the URL path and the "savefileas" value.

That worked for me - make sure you give the full path name in the savefileas value.

sql_Reporter SSCrazy Points: 2329 More actions · Answer 6

I put the script into a ActiveX Script Task in a DTS package, and was getting a errror,

How do call the script from your DTS package ?

sql_Reporter SSCrazy Points: 2329 More actions · Answer 7

Hi am getting this error when i run this activex script. "invalid task result value"

Here is my script , it works fine and downloads the file but i get this error message when it complete

Function Main()

Dim HTTP

Dim Stream

Const adTypeBinary = 1

Const adSaveCreateNotExist = 1

Const adSaveCreateOverWrite = 2

strSource = "http://www.uxb.gbr.com/service/SBS.mdb"

strDest = "\\san03\con\Files\SBS\SBS.mdb"

Set Stream = CreateObject("adodb.stream")

Set HTTP = CreateObject("Microsoft.XMLHTTP")

HTTP.open "GET", strSource, False

HTTP.send

Stream.type = adTypeBinary

Stream.open

Stream.write HTTP.responseBody

Stream.savetofile strDest, adSaveCreateOverWrite

Set Stream = nothing

Set HTTP = nothing

End Function

cliffb SSCarpal Tunnel Points: 4597 More actions · Answer 8

Sorry for not responding before. Work was blocking notifications from sqlservercentral.com and only recently allowed them through.

You just need to set the return value of the function

Put Main = DTSTaskExecResult_Success just before End Function and it should get rid of the error.

sql_Reporter SSCrazy Points: 2329 More actions · Answer 9

hi, yes i see that my self and fixed it, No worries regards the reply. the code you gave me works fine now, many thanks.

sanji_kumar SSC Rookie Points: 40 More actions · Answer 10

How to pass the user name and the password, if required by the url??