Post reply

SSIS - Replace Mutiple Space

  • July 2, 2012 at 3:33 am

    #277148

    Hi All,

    I have Issue with the Replacement of Space in the Columns that come from Excel Source.

    Before I load to the DB from Excel, I need to check if any Mandate Column is not having details.

    Current Issue: In Mandate Columns if some one enters " " space / Spaces, SSIS is detect it as valid String and not resolving it.

    Using ISNULL(REPLACE(TRIM(<>)," ","")

    Please need help on this Issue....!

  • July 2, 2012 at 4:03 am

    #1507668

    Hi, what do you mean by SSIS is not resolving it? Are you getting an error, or output that you do not expect? Can you show us some sample input data and what you would like to see post-processing?

    -----------------------------------------------
    SQL Server posts at my blog, Grapefruitmoon.Net[/url]

  • July 2, 2012 at 5:03 am

    #1507678

    There is no Error, Data Flows with out any issue.

    Example: " SUDHIR" or "SUDHIR " or " SUDHIR "

    when this is the Source data in Excel, comes in to the SSIS and Passes through the Derived Column

    TRIM(" SUDHIR") Output is " SUDHIR" where as it sould be "SUDHIR" with out Spaces

    I Used Replace also

    REPLACE (" SUDHIR "," ","") Still the Output is " SUDHIR ".

    Please need Help....!

  • July 2, 2012 at 5:20 am

    #1507683

    Can you confirm you're mapping the correct column to your destination? If you're not replacing the existing column in the Derived Column transform, can you check that the new column in the output is being used.

    -----------------------------------------------
    SQL Server posts at my blog, Grapefruitmoon.Net[/url]

  • July 2, 2012 at 11:56 pm

    #1508020

    sudhirnune (7/2/2012)

    Hi All,

    I have Issue with the Replacement of Space in the Columns that come from Excel Source.

    Before I load to the DB from Excel, I need to check if any Mandate Column is not having details.

    Current Issue: In Mandate Columns if some one enters " " space / Spaces, SSIS is detect it as valid String and not resolving it.

    Using ISNULL(REPLACE(TRIM(<>)," ","")

    Please need help on this Issue....!

    ISNULL checks for NULL values, not for empty strings.

    You need to use

    REPLACE(TRIM(myColumn)," ","") = ""

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • July 4, 2012 at 12:32 am

    #1508603

    Actual Issue I am Facing is the Trim Function whcih is not removing the Spaces from the Data.

    The Data Source is frm Excel Sheet.

    Please need help to solve it... 🙁

  • July 4, 2012 at 12:43 am

    #1508605

    Read the note in the following reference:

    TRIM (SSIS Expression)

    Maybe you have "different" type of spaces.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply