i want to create a ssis etl package. am new to ssis. still managed to learn basics from internet and started working on it. Source= xml. destination = microsoft sql server database.. 2 separate tables one for good record and another for bad records.
my final result should look like this,, specific column with error should come and sit in the below table,,if there are 20 bad fields in a single xml row then 20 bad records should sit separately in the below table. bad record structure :
[Slno]
[LoanNumber] = this is primary key in my source, so this needs to inserted for every bad data column.
[ErrorField] = i need to insert which input data in xml has error.
[ErrorFieldValue] = i need to insert what is the value of error column.
[ErrorMessage]= and a error message based on the validaiton.
input xml data = it has 5 rows of data in a xml and each row has 100 data fields.
i need to validate each and every data field in xml before putting it into sql database table.
i tried to validate based on data conversion field... example if (Amount) from input data source != float.. redirect the error into sql error table destination.. but while mapping i need to map all good fields or only i can select specific column but if there 20-30 error fields in input data am not able to validate and map error values.
my required validations are lenght validation ,alphanumeric and date .i need to check lenght of field should not be >10 else it should move to error table, like that amount should not be alphanumeric and date should be proper.
please help me to solve this.
