none
replace value

Answers

All replies

  • Lets say you have a row or record containing a bad value or an empty string:

    You need to run and update like so:

    UPDATE Schema.TableName
    SET CASE WHEN CusPhone = '' THEN NULL ELSE CusPhone END

    You can do this to any peice of data that is not define a svalid by your business validation rules.

    Another Example: If the customer Date of Birth is not a valid number then inser a Null value.

    UPDATE Schema.TableName
    SET CASE WHEN ISNUMERIC(CusDob) = 0 THEN NULL ELSE CusDob END

    Just an FYI: ISNUMERIC is not fool proof, this is just an example:

    If you are doing this as an insert, then do this:

    Insert INTO Schema.MyTable(CusDob) SELECT CASE WHEN ISNUMERIC(CusDob) = 0 THEN NULL ELSE CusDob END;

    This will insert a null value if the dob is not valid number.


    OG


    • Edited by Open Galaxy Sunday, September 08, 2013 1:39 AM
    Sunday, September 08, 2013 1:36 AM
  • We can use Derived Column transformation in SSIS.

    Refer the below example link,

    http://stackoverflow.com/questions/6210744/how-to-replace-double-quotes-in-derived-column-transformation


    Regards, RSingh


    • Edited by RSingh() Sunday, September 08, 2013 2:53 AM .
    Sunday, September 08, 2013 2:53 AM
  • Is there a way to do this without manually setting up each column?
    Sunday, September 08, 2013 1:40 PM
  • Hi marybeth,  You do not need to set up each column from the Source in the Derived Column transformation. Add the specific column for which you want to convert failed value with Null. Then you can choose all the source fields along with the new derived column when the final destination table is mapped.


    Regards, RSingh

    Sunday, September 08, 2013 3:11 PM
  • I'm want to set it up to automatically detect which columns fail and automatically set up the derived column transformation/other solution to set the value to null.
    Sunday, September 08, 2013 3:52 PM
  • What is a failed value? If a record fails in your source due an incorrect value, but you want to ignore the value then you should look at the Error Output. Ignoring an error in a column will result in a NULL value.

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Monday, September 09, 2013 5:54 AM