DTS and xml | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

DTS and xml

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
FROM OPENXML(@hDoc, N’/ROOT/Customers’)
WITH Customers Any help would be greatly appreciated! Thanks, V1rt
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? Thanks!
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. Thank!