SQL Server Performance

DTS and xml

Discussion in 'SQL Server DTS-Related Questions' started by v1rtu0s1ty, Aug 25, 2005.

  1. v1rtu0s1ty New Member

    I have written a Visual Basic ActiveX script that connects to an XML gateway, submits an XML input file, and receives and XML response. Currently, the XML response is stored to a variable.

    What I would like to happen is to use that XML response in the variable using openxml. I already have the openxml sql statement but I just don't know how I will call/integrate it within my existing activeX script in DTS. By the way, the openxml I wrote simply does an insert to a table.

    something like this:

    INSERT Customers
    SELECT *
    FROM OPENXML(@hDoc, N'/ROOT/Customers')
    WITH Customers

    Any help would be greatly appreciated!


  2. v1rtu0s1ty New Member

    Looks like I found a solution. What I'm doing now is that I'm saving the results to a text file as c:somedirmyfile.xml

    However, I noticed in Enterprise Manager that the file get saved to the computer you are running EM. If let say you are running EM remotely, the file gets saved on that machine too and not on the server. I know that DTS package is meant for the server but is there a way to force saving it on the server?

  3. v1rtu0s1ty New Member

    Ok guys, I'm ok now. Everything is running like a charm. I'm able to pull XML data from the gateway, and I'm also able to insert the XML response to a temporary data using DTS.

    What icon task in DTS should I use if I want to verify if columns in the temporary table has been properly populated? I would like to know if there are values such as STATE or price fields.


Share This Page