Generate XML file from ActiveX Script Task

• Chintak Chhapia

  Right there with Babe

  Points: 748

  More actions

  May 8, 2005 at 5:14 am

  #186029

  Hi,

  I want to generate XML file from activex script task. I have few global vairables and i want to generate the xml file using those variables.

  Thanks.

  ---

  Kind Regards,

  Chintak Chhapia
  

• David Postlethwaite

  Newbie

  Points: 7

  More actions

  May 9, 2005 at 1:50 am

  #557544

  And what part of that process are you having problems with? We can't help if we don't know what the problem is

• mike gercevich

  SSCrazy

  Points: 2961

  More actions

  May 9, 2005 at 7:31 am

  #557581

  I'm going to take a stab at that you are trying to pass your global variables to a stored procedure that processes them as a FOR XML EXPLICIT select.  From Active-X, you would call your proc like this:

   '*** Build the XML Output Stream

    Set objCommand = CreateObject("ADODB.Command")

    Set objStream = CreateObject("ADODB.Stream")

    objStream.Open

    Set objCommand.ActiveConnection = objConnection

    objCommand.Dialect = "{C8B522D7-5CF3-11CE-ADE5-00AA0044773D}"

    objCommand.CommandType = adCmdStoredProc

    objCommand.CommandText = "MySPName @myparam=" & DTSGlobalVariables("mygvname").value

    objCommand.Properties("Output Stream") = objStream

    objCommand.Properties("XML Root") = "RootTag"

    objCommand.Execute , , 1024   ' adExecuteStream

  Then you can save it with the following set of commands:

   '*** Write the XML Output File

    Dim XMLString

    XMLString = objStream.ReadText (-1) ' adReadAll

    objReportFile.Write( XMLString )  ' Write XML File

    XMLString = ""

    objReportFile.Close   ' Close File

    objStream.Close

    Set objStream = Nothing

    Set objCommand.ActiveConnection = Nothing

    Set objCommand = Nothing

  Not sure if that is what you are after... but hope it helps someone.

  -Mike Gercevich