dcsimg
Results 1 to 2 of 2

Thread: need help ASAP (data cleansing Using SSIS)

Hybrid View

  1. #1
    Join Date
    Nov 2010
    Posts
    1

    need help ASAP (data cleansing Using SSIS)

    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.

  2. #2
    Join Date
    Feb 2006
    Posts
    4
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •