SSIS Error

Question

Post reply

SSIS Error

parthi-1705

SSCrazy Eights

Points: 9277
More actions
October 12, 2010 at 7:34 am

#241528

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

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

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

you can't use case statements in ssis expressions

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

cliffb SSCarpal Tunnel Points: 4597 More actions · Answer 2

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 More actions · Answer 3

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

Thanks

Parthi

Thanks
Parthi

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 4

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 More actions · Answer 5

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

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 6

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 More actions · Answer 7

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 More actions · Answer 8

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