December 2, 2009 at 8:35 am
I am importing Flat file in to Database table. There are two column which is col1 of flat file is Called_data in database table, col2 is is called_time both database column are set to Datetime datatype. i was using transformation task of derved column and used expression for col1. Col1 and Col2 have length of 6 in Flat flie and value for col1= 090902 for date, col2= 082124 for time
my expression for col1 was
(DT_Date)(SUBSTRING([Column 1],5,2) + "-" + SUBSTRING([Column 1],1,2) + "-" + SUBSTRING([Column 1],3,2))
it return value 2009-02-09 00:00:00.000
don't know how it is formatted since origional value was 090902
and now i have no clue about getting time on the right hand side for the col2 where we don't care about date.
Please help me out. i have been stuck for couple day on this.
Thanks
Sagar
December 2, 2009 at 9:06 am
It sounds like you want one column with date AND time. IF that is true then this is probably the answer:
(DT_Date)(SUBSTRING([Column 1],5,2) + "-" + SUBSTRING([Column 1],1,2) + "-" + SUBSTRING([Column 1],3,2) + " " + SUBSTRING([Column 2],1,2) + ":" + SUBSTRING([Column 2],3,2) + ":" + SUBSTRING([Column 2],5,2) )
If that isn't what you were looking for then I misunderstood..
CEWII
December 2, 2009 at 9:36 am
Perfect thanks a lot 🙂
December 2, 2009 at 10:01 am
You are welcome.
CEWII
December 2, 2009 at 1:02 pm
I have one question though, when there is blank or null value for date in flat file looks like it through error should i ignore the error outupt to ignore or is there any method to fix that without ignoring the error output.
Thanks
Sagar
December 2, 2009 at 1:53 pm
You might do a check to see if it is null or blank before you do the conversion. Or you could add a derived column task before this and do a test for NULL or blank with something like where you do replacements:
ISNULL( [Field1] ) == True || [Field1] == "" ? "19000101" : [Field1]
What this says is that if Field1 is null or blank replace it with 19000101, otherwise just pass the value through..
CEWII
December 2, 2009 at 11:39 pm
I added the derived column transformation task right before the date conversion and added expression as below still error
ISNULL([Column 5]) == TRUE || [Column 5] == " " ? "01/01/1900" : [Column 5]
I am getting error like this
[Derived Column [251]] Error: An error occurred while attempting to perform a type cast.
[Derived Column [251]] Error: The "component "Derived Column" (251)" failed because error code 0xC0049064 occurred, and the error row disposition on "output column "convertCol 5" (336)" specifies failure on error. An error occurred on the specified object of the specified component.
December 3, 2009 at 8:11 am
Do you know if the record failing is NULL or blank? Also your code shows " " and not "" there is a difference. Also you should put the date into the same format that you would get from the file so instead of 01/01/1900 it would be 19000101..
CEWII
December 3, 2009 at 8:21 am
record in flatfile is blank, and when i igonered error output it stored in db as null. Date column comes from flat file as 09/02/2009 and some of this column in flat file is blank. That is why i was trying that way. so is that wrong?
December 3, 2009 at 9:26 am
Probably not wrong but you need to test for the right value. You check for blank with "", with " " you are checking for a space. It seems the hangup is getting it to realize that you don't have a value and want to replace it.. You might have to play with the value a bit to get it right but I would start with "".
CEWII
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply