troubleshooting Question

SQL DTS package ---XML file generation

Avatar of Mani Pazhana
Mani PazhanaFlag for United States of America asked on
Microsoft SQL Server
15 Comments2 Solutions810 ViewsLast Modified:
Hello Experts,

I am generating an xml file from my DTS package. The xml file is of UTF-8 encoding format.

The end users are complaining the file as Hexa value characters at the very begining of the xml file, which i dont see from IE.

Here is my DTS active x vbscript:

----------------------------------------------------
'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************
 

Option Explicit

 

Function Main()

      Const FileName = "C:\IESFileDataConversionXML\ProcessInputFile\GenerateXMLFile.xml"
      Dim cn, cmd, stmOut, stmIn
      Set cn = CreateObject("ADODB.Connection")

With cn
          .Open = "Provider=SQLOLEDB.1;Data Source=INFOTECHD99\MANI;Initial Catalog=IESData;user id =sa;password=test"
 End With

      Set stmOut = CreateObject("ADODB.Stream")

With stmOut
          .Open
          .Type = 2               ' adTypeText
      .Charset = "UTF-8"
          .WriteText "<?xml version=""1.0"" encoding=""UTF-8""?>"
          .WriteText "<IPA_ZFI_COPA_FB50>"

End With

      Set cmd = CreateObject("ADODB.Command")
With cmd

          Set .ActiveConnection = cn
          .CommandType = 4            ' adCmdStoredProc
          .CommandText = "sp_GetXmlHeaderForDTS"
          Set stmIn = CreateObject("ADODB.Stream")

   With stmIn

              .Open
              .Type = 2               ' adTypeText

   End With

          .Properties("Output Stream").Value = stmIn
          .Execute , , 1024               ' adExecuteStream
      stmOut.WriteText stmIn.ReadText
With stmOut               
      .WriteText "<Body>"
End With


          .CommandText = "sp_GetXmlBodyForDTS"
          .Execute , , 1024               ' adExecuteStream
          stmOut.WriteText stmIn.ReadText
          stmIn.Close
          Set stmIn = Nothing

End With

      Set cmd = Nothing

With stmOut

               .WriteText "</Body>"
              .WriteText "</IPA_ZFI_COPA_FB50>"
              .SaveToFile FileName, 2         ' adSaveCreateOverWrite
          .Close
End With

Set stmOut = Nothing
cn.Close
Set cn = Nothing

Main = DTSTaskExecResult_Success

End Function

--------------------------------------------------------------------------

any idea what is wrong with my code above, or to fix this issue.


ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 15 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 15 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros