|
Home > Archive > MS SQL Server DTS > October 2005 > Can xml download be done w/o a browser using dts?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Can xml download be done w/o a browser using dts?
|
|
| Boyd S 2005-10-27, 7:57 am |
| I need to find a way for a dts package to retrieve an XML file that exists on
a website and retrieve the file on a daily schedule. We tried a dts package
that uses Activex to open a browser and retrieve the file, which works when
we run the package while logged in. BUT, when SQLServerAgent executes the
package as scheduled, the package fails because it cannot create the browser
to retrieve the file. Does someone know how to retrieve the file in a way
that does not require a browser? Or do it in a way that be scheduled to run
vai SQLServerAgent?
Please?
--
Boyd Sommerfeldt
developer / dba
VECO Canada
| |
|
| The following works for me as a scheduled job:
'*******************
********************
********************
*************
' Visual Basic ActiveX Script
'*******************
********************
********************
*************
Option Explicit
Function Main()
Dim mInternet, mFile, mFiletxt, fso
Dim theMonth, theYear, WebAddress, filePath, destPath
If month(date) <> 1 Then
theMonth = month(date) - 1
theYear = year(date)
Else
theMonth = 12
theYear = year(date) - 1
End If
WebAddress = "http://www.mywebsite/mypage.asp?vend=00000&month=" &
theMonth & "&year=" & theYear
If theMonth < 10 Then
destPath = "D:\Data\myfile" & theYear & "_0" & theMonth & ".html"
Else
destPath = "D:\Date\myfile" & theYear & "_" & theMonth & ".html"
End If
filePath = "D:\Data\myfile.html"
Set mInternet= CreateObject("Microsoft.XMLHTTP" )
mInternet.open "GET" , WebAddress, false
mInternet.send
mFiletxt= mInternet.Responsetext
set mInternet = nothing
set fso = createObject("scripting.FilesystemObject")
set mfile = fso.createtextfile("filePath")
mfile.write mfiletxt
mfile.close
If (fso. FileExists(filepath)
) Then
fso.MoveFile filepath, destPath
End If
set mfile = nothing
set fso = nothing
Main = DTSTaskExecResult_Su
ccess
End Function
"Boyd S" wrote:
> I need to find a way for a dts package to retrieve an XML file that exists on
> a website and retrieve the file on a daily schedule. We tried a dts package
> that uses Activex to open a browser and retrieve the file, which works when
> we run the package while logged in. BUT, when SQLServerAgent executes the
> package as scheduled, the package fails because it cannot create the browser
> to retrieve the file. Does someone know how to retrieve the file in a way
> that does not require a browser? Or do it in a way that be scheduled to run
> vai SQLServerAgent?
>
> Please?
> --
> Boyd Sommerfeldt
> developer / dba
> VECO Canada
| |
| Darren Green 2005-10-27, 7:57 am |
| You may want to look at ServerXMLHTTP, as that is designed for unattended
operations.
e.g. MSXML2.ServerXMLHTTP.4.0
"Dave" <Dave@discussions.microsoft.com> wrote in message
news:5FFBCC5B-748D-4E47-888A- 990A9F646901@microso
ft.com...[color=darkred]
> The following works for me as a scheduled job:
>
> '*******************
********************
********************
*************
> ' Visual Basic ActiveX Script
> '*******************
********************
********************
*************
> Option Explicit
>
> Function Main()
>
> Dim mInternet, mFile, mFiletxt, fso
> Dim theMonth, theYear, WebAddress, filePath, destPath
>
> If month(date) <> 1 Then
> theMonth = month(date) - 1
>
> theYear = year(date)
> Else
> theMonth = 12
> theYear = year(date) - 1
> End If
>
> WebAddress = "http://www.mywebsite/mypage.asp?vend=00000&month=" &
> theMonth & "&year=" & theYear
>
> If theMonth < 10 Then
> destPath = "D:\Data\myfile" & theYear & "_0" & theMonth & ".html"
>
> Else
> destPath = "D:\Date\myfile" & theYear & "_" & theMonth & ".html"
> End If
>
> filePath = "D:\Data\myfile.html"
>
> Set mInternet= CreateObject("Microsoft.XMLHTTP" )
>
> mInternet.open "GET" , WebAddress, false
> mInternet.send
> mFiletxt= mInternet.Responsetext
> set mInternet = nothing
>
> set fso = createObject("scripting.FilesystemObject")
>
> set mfile = fso.createtextfile("filePath")
> mfile.write mfiletxt
> mfile.close
>
> If (fso. FileExists(filepath)
) Then
> fso.MoveFile filepath, destPath
> End If
>
> set mfile = nothing
> set fso = nothing
>
> Main = DTSTaskExecResult_Su
ccess
> End Function
>
>
> "Boyd S" wrote:
>
|
|
|
|
|