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.

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

        Catch ex As Exception


            'Close the stream

        End Try

        'The document is valid

        Dts.TaskResult = Dts.Results.Success
    End Sub


End Class
This article has been dead for over six months. Start a new discussion instead.