ActiveX and SQL Server question?

Apologies for posting this in the wrong section, now pointed to here.

I have the following

AciveX script set up as a DTS package which I would like to alter so

that the output is sent to another server. :-

Function Main()
dim oCmd, sSql, oDom
set oDom = CreateObject("Msxml2.DOMDocument")
set oCmd = CreateObject("ADODB.Command")
oCmd.ActiveConnection = "Provider=SQLOLEDB; Data Source=MyServer; Initial Catalog=MyDB; Integrated Security=sspi"

sSql = "<ROOT

xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:query>SELECT

* FROM VW_Projs1 FOR XML AUTO</sql:query></ROOT>"
oCmd.CommandText = sSql
oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
oCmd.Properties("Output Stream") = oDom
oCmd.Execute, , 1024
oDom.Save "C:\Inetpub\wwwroot\test.xml"
Main = DTSTaskExecResult_Success
End Function

I want to schedule the package so it runs overnight, so
1.

Do I have to change the 'Integrated Security=sspi' part of the script

to reflect a user who has suitable rights to create/write a file on the

other server? If so How?
2. Would I redirect to the another

server/directory just by changing the path in oDom.Save to

"ServerName\DirectoryName".? or would there be a different format?

Thanks

I don't have much exp. in ActiveScript but I can say that if you are creating a flat file on a different server apart from the local server then you need to define the path correctly \\servername\path in order to create the file. Also ensure to check the SQLAgent permissions on the specified path as you're going to schedule this as a job.

Also you might try creating the file locally on the server and then copy the same using copy command in SQL scheduled job.