I tried DTS and SSIS both. But it seems there's no way to import data
from a web page.
Basically, my client has a php page on their server. I can access the
file by using an URL (for example http://myclient/page.php?id=56). And
this will return a XML file.

in SSIS, i can easily import a XML file on my local system, match the
columns and import it into my database. But how can i do it while its
on the web?

(I have tried the HTTP connection manager, but it seems it's for web
service only)

This is not a kind of import I have done, but my initial thoughts are these:
1) Create a script file that imports the System.xml namespace.
2) In your script, create an XML document instance.
3) Use the LoadFromURL method of the XML document object to load from the
URL into a package variable.
4) Add a Data Flow task to the package.
5) Use the XML Source component to import from the variable. You need to
have created an XSD schema file for the XML input.

Notice that an HTTP connection manager is not required.

Charles Kangai, MCT, MCDBA
Author of Learning Tree's 4-day course: "SQL Server 2005 Integration
Services" http://www.learningtree.com/courses/134.htm
Author of Learning Tree's 4-day course: "SQL Server Reporting Services"
http://www.learningtree.com/courses/523.htm
email alias: charles
email domain: kangai.demon.co.uk




"Alcibiades (AT) gmail (DOT) com" wrote:

I have tested the code below, and it works in the Script Task. You then need
to add the Data Flow task after this task, then add XML Source. For the Data
Access mode, choose XML data from variable, and specify the variable you read
the XML into. You need to have a schema. Here it is:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Xml

Public Class ScriptMain
Public Sub Main()

Dim xmlDoc As XmlDataDocument, strXML As String
xmlDoc = New XmlDataDocument
xmlDoc.Load("http://localhost/MyXMLFile.xml")
strXML = CType(xmlDoc.InnerXml, String)
Dts.Variables("strXMLData").Value = strXML
Dts.TaskResult = Dts.Results.Success
End Sub

End Class





"Alcibiades (AT) gmail (DOT) com" wrote:

Charles,
Thanks a lot!
I am trying it now.

Charles Kangai wrote: