Member Avatar for Hussain27syed

Hi.
I am working on reading data from a XML file and upload to sql server 2005.
The XML file has many rows of data stored. Each data row node has many subnodes. I have to validate the XML against the schema. The problem is if a particular data row node fails , i have to store some information( Could be row number or anything) in a text file.
Could anyone please help me on how this can be done.
I am using Vb.NET in Script task of SSIS package.

  • 1 Contributor
  • 1 Reply
  • 400 Views
  • 23 Hours Discussion Span
  • Latest Post Latest Post by Hussain27syed
Member Avatar for Hussain27syed

PFB the ocde i am using.
With the exception message i can get the information as to why the validation failed but, i want some way to get any info about which record caused the failure.
Even the line number where validation failed would be a great help

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Option Strict Off
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Tasks.xmltask
Imports System.Xml
Imports System.Xml.XmlNamespaceManager
Imports System.Xml.XmlDocument
Imports System.IO
Imports System.Text

Public Class ScriptMain

      ' The execution engine calls this method when the task executes.
      ' To access the object model, use the Dts object. Connections, variables, events,
      ' and logging features are available as static members of the Dts class.
      ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
      ' 
      ' To open Code and Text Editor Help, press F1.
      ' To open Object Browser, press Ctrl+Alt+J.

    Public Sub Main()


        Dim sFile, sSchemaFile As String

        'sFile = Dts.Variables("sFilePath").Value + Dts.Variables("sFileName").Value + Dts.Variables("sFileExtension").Value
        'sSchemaFile = Dts.Variables("sSchemaFilePath").Value + Dts.Variables("sSchemaFileName").Value

       
        If System.IO.File.Exists(sFile) Then     ' check if XML file Exists

        End If

        If System.IO.File.Exists(sSchemaFile) Then     ' check if XML Schema file Exists

        End If



        Dim objWorkingXML As New System.Xml.XmlDocument
        Dim objValidateXML As System.Xml.XmlValidatingReader
        Dim objSchemasColl As New System.Xml.Schema.XmlSchemaCollection



        objSchemasColl.Add("", sSchemaFile)
        objValidateXML = New System.Xml.XmlValidatingReader(New System.Xml.XmlTextReader(sFile))

        'This is WHERE the validation occurs.. WHEN the XML Document READS through the validating reader

        Try
            objWorkingXML.Load(objValidateXML)
            MsgBox("Validated")
        Catch ex As Exception

            MsgBox(ex.Message.ToString)

        Finally
            'Close the stream
            objValidateXML.Close()

        End Try

        'The document is valid

        Dts.TaskResult = Dts.Results.Success
    End Sub

    

End Class
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.