SSIS Error

  • parthi-1705

    SSCrazy Eights

    Points: 9277

    hi

    I am having a text file with columns fname,lname,BirthDate....startdate etc...

    i have used derived column in dataflow to convert

    i will be receiving the text file in that BirthDate will be 10031980

    so i need to put as 10/03/1980 so i did as

    (DT_DATE)(SUBSTRING(BirthDate,1,2) + "/" + SUBSTRING(BirthDate,3,2) + "/" + SUBSTRING(BirthDate,5,4))

    startdate will be like 10132010, so i need to put as 10/13/2010

    here some times i may receive ''(null) string also meaning that start date has not given so it will be empty

    if startdate is null or empty how to handle

    (DT_DATE)(SUBSTRING(startdate,1,2) + "/" + SUBSTRING(startdate,3,2) + "/" + SUBSTRING(startdate,5,4))

    so i tried with case statement as like TSQL but it is not getting loaded

    case when len(startdate)>0 then

    (SUBSTRING(startdate,1,2) + "/" + SUBSTRING(startdate,3,2) + "/" + SUBSTRING(startdate,5,4))

    else '' end

    error :

    Error at Data Flow Component [Derived Column [2481]]: Attempt to parse the expression "case when len(StartDate)>0 then (SUBSTRING(StartDate,1,2) + "/" + SUBSTRING(StartDate,3,2) + "/" + SUBSTRING(StartDate,5,4)) else '' end " failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.

    Error at Data Flow Component [Derived Column [2481]]: Cannot parse the expression "case when len(StartDate)>0 then (SUBSTRING(StartDate,1,2) + "/" + SUBSTRING(StartDate,3,2) + "/" + SUBSTRING(StartDate,5,4)) else '' end ". The expression was not valid, or there is an out-of-memory error.

    Error at Data Flow Component [Derived Column [2481]]: The expression "case when len(StartDate)>0 then (SUBSTRING(StartDate,1,2) + "/" + SUBSTRING(StartDate,3,2) + "/" + SUBSTRING(StartDate,5,4)) else '' end " on "input column "StartDate" (3061)" is not valid.

    Error at Data Flow Component [Derived Column [2481]]: Failed to set property "Expression" on "input column "StartDate" (3061)".

    (Microsoft Visual Studio)

    how to resolve it

    Thanks

    Parthi

    Thanks
    Parthi

  • steveb.

    SSC-Forever

    Points: 46733

    you can't use case statements in ssis expressions

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

  • cliffb

    SSCarpal Tunnel

    Points: 4547

    You need to use the tertiary operator.

    (CONDITION) ? Expression if True : Expression if false.

    EXAMPLE:

    (ISNULL(DateColumn) ? (DT_DATE)"1900-01-01" : Your date experession

  • parthi-1705

    SSCrazy Eights

    Points: 9277

    then how can i use here to check any other syntax for the above

    Thanks

    Parthi

    Thanks
    Parthi

  • parthi-1705

    SSCrazy Eights

    Points: 9277

    cliffb (10/12/2010)


    You need to use the tertiary operator.

    (CONDITION) ? Expression if True : Expression if false.

    EXAMPLE:

    (ISNULL(DateColumn) ? (DT_DATE)"1900-01-01" : Your date experession

    is this correct

    StartDate=""? "":(SUBSTRING(StartDate,1,2) + "/" + SUBSTRING(StartDate,3,2) + "/" + SUBSTRING(StartDate,5,4))

    thanks

    Parthi

    Thanks
    Parthi

  • steveb.

    SSC-Forever

    Points: 46733

    i'm a bit confused; are your date mm/dd/yyyy or dd/mm/yyyy

  • parthi-1705

    SSCrazy Eights

    Points: 9277

    steveb. (10/12/2010)


    i'm a bit confused; are your date mm/dd/yyyy or dd/mm/yyyy

    any thing dd/mm/yyyy or mm/dd/yyyy or yyyy/mm/dd thats not a matter i need to split and store so dont get confuse on formats

    Thanks

    Parthi

    Thanks
    Parthi

  • steveb.

    SSC-Forever

    Points: 46733

    parthi-1705 (10/12/2010)


    steveb. (10/12/2010)


    i'm a bit confused; are your date mm/dd/yyyy or dd/mm/yyyy

    any thing dd/mm/yyyy or mm/dd/yyyy or yyyy/mm/dd thats not a matter i need to split and store so dont get confuse on formats

    Thanks

    Parthi

    so how are you going to tell the difference for example 10/10/2010

    is there a flag in the file ?

  • parthi-1705

    SSCrazy Eights

    Points: 9277

    steveb. (10/12/2010)


    so how are you going to tell the difference for example 10/10/2010

    is there a flag in the file ?

    no i need the output as of now then i will format it, so it be as mm/dd/yyyy

    Thanks

    Parthi

    Thanks
    Parthi

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

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