Excel sheet validation of data for SSIS

• manithan_it

  Newbie

  Points: 1

  More actions

  July 18, 2010 at 11:30 am

  #224978

  Hi,

  I have tried to validate the Excel sheet data, which will contains some 13 fields including Cost, First name, LastName, Date etc.,

  SSIS will do the Transformation in the Data flow, but we cannot validate the contents of the Cell in Data flow and raise error.

  1. For eg: the Cost field validation for Only Numbers like 12 or 12.34 or 12.3 or 12.00, ie only Amount not some chars like 12a, 12b, then we have to raise error.

  2. When the date field does not have any valid date with format dd/mm/yyyy, then raise error.

  We can do this only through the Script task in the Control flow, by using the vb.net code to externally connect to the excelsheet and validate the fields and raise error before transformation of the data in Data flow.

  I have given below the code for this Excel sheet data validation in vb.net, which will surely be useful for all the SSIS developer community and vb.net developers.

  ********************************************************************************************************************

  Try

  Dim dataSet As DataSet

  Dim dataRow As DataRow

  Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

  Dim expensesDataFle As String = "C:\Expense.xls"

  Dim expensesRowCount As Int32

  'Connection to Excel using Jet Engine

  MyConnection = New System.Data.OleDb.OleDbConnection( _

  "provider=Microsoft.Jet.OLEDB.4.0; " & _

  "data source=" & expensesDataFle & "; " & _

  "Extended Properties = Excel 8.0")

  'Select the data from sheet name "Expense Sheet"

  MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Expense Sheet$]", MyConnection)

  dataSet = New System.Data.DataSet()

  MyCommand.Fill(dataSet)

  'To get the number of columns in the Excel Sheet

  Dim columnCount As Integer = dataSet.Tables(0).Columns.Count

  'Check whether the hader Columns count is equal to 10

  If(columnCount=10)

  'Validate the 10 header Columns in the Excel sheet

  if Not( dataSet.Tables(0).Columns(0).ToString.ToUpper.Equals("DATE APPROVED") And _

  dataSet.Tables(0).Columns(0).ToString.ToUpper.Equals("EXPENSE CODE") And _

  dataSet.Tables(0).Columns(0).ToString.ToUpper.Equals("DEPARTMENT") And _

  dataSet.Tables(0).Columns(0).ToString.ToUpper.Equals("COST") And _

  dataSet.Tables(0).Columns(0).ToString.ToUpper.Equals("USER") And _

  dataSet.Tables(0).Columns(0).ToString.ToUpper.Equals("FIRST NAME") And _

  dataSet.Tables(0).Columns(0).ToString.ToUpper.Equals("LAST NAME") And _

  dataSet.Tables(0).Columns(0).ToString.ToUpper.Equals("INITIAL") And _

  dataSet.Tables(0).Columns(0).ToString.ToUpper.Equals("BIRTH DATE") And _

  dataSet.Tables(0).Columns(0).ToString.ToUpper.Equals("VAT") ) Then

  'initialise the row count to zero

  expensesRowCount = 0

  'Loop through the dataset to validate the fields in "BIRTH DATE", "EXPENSE CODE", "COST" columns

  For Each dataRow In dataSet.Tables(0).Rows

  ExpensesRowNumber = expensesRowNumber + 1

  Dim birthDate As String = Trim(dataRow("BIRTH DATE").ToString())

  Dim expenseCode As String = trim(dataRow("EXPENSE CODE").ToString())

  Dim Cost As String = Trim(dataRow("COST").ToString())

  'Raise error when invalid data in a row

  If Not (birthDate.Length = 8 And IsNumeric(birthDate) And IsNumeric(expenseCode) And IsNumeric(Cost)) Then

  WriteVariable("ErrorRowNumber", expensesRowNumber)

  WriteVariable(MessgeID","123")

  Dts.TaskResult = Dts.Results.Failure

  Exit Sub

  EndIf

  Next

  Else

  'Set the message id and fail validation

  WriteVariable("MessageId","124")

  Dts.TaskResult = Dts.Results.Failure

  Exit Sub

  EndIf

  Else

  'Set the message id and fail validation

  WriteVariable("MessageId","125")

  Dts.TaskResult = Dts.Results.Failure

  Exit Sub

  EndIf

  Catch ex As Exception

  'Set the message id and fail validation

  WriteVariable("MessageId","126")

  Dts.TaskResult = Dts.Results.Failure

  Exit Sub

  FInally

  MyConnection.Close()

  MyConnection.Dispose()

  End Try

  ********************************************************************************************************************

  Excel Sheet Format

  ==============

  DATE APPROVED ¦ EXPENSE CODE ¦ DEPARTMENT ¦ COST ¦ USER ¦ FIRST NAME ¦ LAST NAME ¦ INITIAL ¦ BIRTH DATE ¦ VAT

  03122010 1234 1234 123.34 MK Kalai Chelvan V 13041982 17

  03122010 1234 1234 123.34 MK Vinoth Chelvan V 13041982 17

  03122010 1234 1234 123.34 MK Mani Chelvan V 13041982 17

  Thanks,

  Mani

• July 21, 2010 at 5:01 am

  This was removed by the editor as SPAM