Post reply

SSIS derived column transformation failing on converting Blank rows from excel while converting the column from string to int

  • July 30, 2015 at 2:51 am

    #302958

    Hi,

    I have flat file source from which data is imported to a Sql table.The target column is int and input column is string .The column has some numeric values and some blank values.when I tried to convert into int values it fails.Please help!!!

  • July 30, 2015 at 3:32 am

    #1816035

    Hi

    What is the expression on your derived column transformation ?

    Maybe it is better to use the Convert transformation ?

    If it does not work you can handle blank value with the token "?" which the same as an IIF

    <boolean expression>?<True>:<False>

    :w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:

  • July 30, 2015 at 3:37 am

    #1816038

    I tried but the input columns has some blank values...so conversion to int fails.It has values as below

    <blank>

    <blank>

    38761

    38738

    38740

    38741

    38742

    38743

    38744

  • July 30, 2015 at 3:54 am

    #1816039

    ok

    did you try the expression with "?"

    FieldValue == "" ? (DT_I4) 0 : (DT_I4) FieldValue

    :w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:

  • July 30, 2015 at 4:11 am

    #1816046

    Yes..I tried still it fails..

  • July 30, 2015 at 6:10 am

    #1816079

    what is the error message ?

    maybe you also have to handle NULL Values ?

    :w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:

  • July 30, 2015 at 8:23 am

    #1816148

    It says type cast error.I get error during run time..

  • July 30, 2015 at 8:52 am

    #1816166

    Mohamed I. (7/30/2015)

    ok

    did you try the expression with "?"

    FieldValue == "" ? (DT_I4) 0 : (DT_I4) FieldValue

    And with this :

    (ISNULL(FieldValue) OR (DT_WSTR,50) (TRIM(FieldValue))) == "" ? (DT_I4) 0 : (DT_I4) TRIM(FieldValue)

    :w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:

  • July 14, 2016 at 2:56 pm

    #1890205

    I had a problem when I tried to load some data with XML where SSIS refused to manipulate the blank space. I couldn't use TRIM or REPLACE. I had to go into C# with a script component and remove the space with something like:

    VARIABLE.ToString().Replace(" ", string.Empty).Trim();

    I hope this can help someone else. I'm not much of a C# coder, but the gist of it is removing the space in a script component.

Viewing 9 posts - 1 through 8 (of 8 total)

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