SSIS Excel Source Data Correction

• durga palepu

  Say Hey Kid

  Points: 701

  More actions

  August 20, 2011 at 5:45 am

  #150458

  Hi all,

  I came across below scenario

  I have an excel file with columns EMPID, EMPNAME, GENDER where gender has values 'M' and 'F'. By mistake 'M' and 'F' used interchangingly i.e. for males gender is marked as 'F' and for females gender marked as 'M'. The problem is to correct this gender mismatch issue using SSIS.

  Let me know if anybody have suggestions.

  Thanks in advance,

  Durga

• Russel Loski

  SSCertifiable

  Points: 7059

  More actions

  August 21, 2011 at 2:44 pm

  #1371202

  I am going to assume that all "M"s from the Excel source needs to be changed to "F" in the target and all "F"s need to be changed to "M."

  My suggestion is to use a derived column transformation (http://sqlblog.com/blogs/andy_leonard/archive/2009/02/04/ssis-expression-language-and-the-derived-column-transformation.aspx, http://www.bimonkey.com/2009/08/the-derived-column-transformation/ and http://pragmaticworks.com/cheatsheet/).

  The transform I would make is [GENDER]=="M"?"F":[GENDER]=="F"?"M":"U"

  This is a case sensitive comparison.

  Russel Loski, MCSE Business Intelligence, Data Platform

• durga palepu

  Say Hey Kid

  Points: 701

  More actions

  August 22, 2011 at 4:41 am

  #1371332

  Dear Russel Loski,

  You are absolutely correct!!

  Thank you, it helped me to resolve the issue.:-)

  Regards,

  Durga