need help ASAP (data cleansing Using SSIS)

[mhassanshahbaz]

i have converted many text,acess,acess,oracle data to sql now i have to perform data cleansing.

i ,m new to SSIS and i don`t know how to use SSIS to perform my data cleansing before ETL. i have used some queries in my SQL to check dirty data,

for gender i found 8 values with (00,11,01,10,001,M) .

similarly, for date of birth
12 invalid dates. 22-Jal-75,1/27/75,27-Apl-77….. 29-Feb-75,31-jun-89 etc.

WHAT I WANT????

1)first SSIS read data.

2)if don`t find 0 or 1 in gender send erronous data(00,11,01,10,001,M) to “error table”.

3)in “error table”, dirty data (00,11,01,10,001,M) should be converted in 0,1

4)after standarization, error table data records should go to original table at its place.

5)Now i will change 0 to male and 1 to female.

6)now i will send complete table of student(stid,name,father,gender,adress,last degree,reg data,dob) to a new table STD_INFO.

7) i will repeat same 1-6 steps for all databases(lahore,karachi,islamabad,peshawar) and then i will integrate STD_INFO tables of all databases into one.

i hope now you can understand what i have to do. Similar case with DOB first dirty data to erronous table after conversion there to original table and then i will check all records at the same time.

[wcikanek]

What I do is create a work table in DB and load all data. Then I need only to write one set of scripts to search for all bad data. The fix package contains scripts to run to correct the bad data. Then the package to move data to production table is just a record to record movement.