none
unwanted characters in my row in sql table

    Question

  • i have colum like this

    Id    gender

    1        m

    2       f

    3      null

    4      x

    i want dont want x kind of letter i need only M and F.can any one explain me or tell me  what is the stored procedure for that i need mail that error on my email id.

    Tuesday, December 06, 2011 8:09 PM

Answers

All replies

  • you can use a conditional split and use this expression:

    LOWER([gender])=="m" || LOWER([gender])=="f"

    and name this output as validOutput

    and name default output as InvalidOutput

    then redirect invalidoutput to an excel file destination,

    and after the data flow task use a send mail task or script task to send a mail to your email id with excel file attached


    http://www.rad.pasfu.com
    Tuesday, December 06, 2011 8:33 PM
  • Hi sqlreminder,

     

    Clean up your table and apply a Check Constraint.

    http://msdn.microsoft.com/en-us/library/ms179491.aspx

    If you need help cleaning it up, just Select DISTINCT(gender) from table, then UPDATE table where gender = 'x' or gender IS NULL to either M or F.

    Tuesday, December 06, 2011 8:44 PM
  • Or you can use the TSQL query to select only records from source where Gender=F or M.

    E.g.

     

    Create table #T( ID INT, GENDER CHAR(1))

    go

    insert into #T values(1,'M')

    ,(2,'F'),(3,NULL),(4,'X')

     

     --T SQL Query that you can use in OLE DB Source to extract records from your Source Table where Gender=M or F only.

    Select ID,Gender from #T 

    where GENDER='M' OR GENDER='F'  

     

    When you say, you need to email error to your mail id, What Error? Do you want to email all the records to your email address those has wrong gender information?

    if yes, then you can use sp_send_dbmail. you will be using Execute SQL task with sp_send_dbmail procedure to email these records to you.

    Thanks

    Aamir


    http://sqlage.blogspot.com/
    Tuesday, December 06, 2011 8:49 PM
  • hey Amir thanx but how should i call this procedure in ssis should i use ole db command or i have to use sql task?

     

    Tuesday, December 06, 2011 8:59 PM
  • Tuesday, December 06, 2011 9:21 PM
  • Hi sqlreminder,

    Here is a example about using Stored Procedures in Execute SQL Task: http://decipherinfosys.wordpress.com/2008/04/14/execute-sql-tasks-using-stored-procedures/

    Thanks,
    Eileen
    • Marked as answer by Eileen Zhao Wednesday, December 14, 2011 7:39 AM
    Thursday, December 08, 2011 8:36 AM