July 17, 2008 at 8:31 am
Hi,
i face problems in date conversion, when i try to insert a date into the table column(timestamp column)
Following is my dataflow:
flatfile source -> derived column -> ole db destination
******Flatfile source:
flatfile contains the date in following format : YYYYMMDD (eg:20080718)
since the destination table column is datetime datatype, i read this as a DT_STR datatype.
******Derived column:
i read the date column as a string in the flatfile source, and then i do the following in the derived column transformation -
(DT_DBTIMESTAMP)(SUBSTRING(DT,7,2) + "-" + SUBSTRING(DT,5,2) + "-" + SUBSTRING(DT,1,4))
where DT is the name i give to the date column while reading the file
******ole db destination:
i try to insert this derived column into the destination table. its giving me the following error:
[OLE DB Destination [43]] Error: There was an error with input column "DT" (81) on input "OLE DB Destination Input" (56). The column status returned was: "The value could not be converted because of a potential loss of data.".
[OLE DB Destination [43]] Error: The "input "OLE DB Destination Input" (56)" failed because error code 0xC0209077 occurred, and the error row disposition on "input "OLE DB Destination Input" (56)" specifies failure on error. An error occurred on the specified object of the specified component.
Can anyone of you tel how to resolve it?
Thanks,
Arunvijay
July 17, 2008 at 9:54 am
Aveerabadran are you sure that the column data type is timestamp. and not a datetime data type.
From BOL
Is a data type that exposes automatically generated, unique binary numbers within a database. timestamp is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The timestamp data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime data type
July 17, 2008 at 10:14 am
Sorry for not posting in the correct Etiquette . it was actually datetime datatype only (it was my typo error)
----------Use the following command to create the test table:
create table hello_123 (col_1 varchar(2),DT datetime)
Since the source is a flat file , no need for us to insert data into it.
----------Following is the flatfile that i use to load:
AV20080617
DB20080704
The flat file is fixed width delimited.
positions 1-2 goes into col_1
positions 3-10 goes into DT
Now comes my transformation explanation:
******Flatfile source:
since the destination table column is datetime datatype, i read this as a DT_STR datatype.
******Derived column:
i read the date column as a string in the flatfile source, and then i do the following in the derived column transformation -
(DT_DBTIMESTAMP)(SUBSTRING(DT,7,2) + "-" + SUBSTRING(DT,5,2) + "-" + SUBSTRING(DT,1,4))
where DT is the name i give to the date column while reading the file
******ole db destination:
i try to insert this derived column into the destination table. its giving me the following error:
[OLE DB Destination [43]] Error: There was an error with input column "DT" (81) on input "OLE DB Destination Input" (56). The column status returned was: "The value could not be converted because of a potential loss of data.".
Pls let me know reason for this error.
July 17, 2008 at 11:03 am
Aveerabadra
I am not that familiar with SSIS but I see you are using
(DT_DBTIMESTAMP)(SUBSTRING(DT,7,2) + "-" + SUBSTRING(DT,5,2) + "-" + SUBSTRING(DT,1,4))
have you tried using DT_DBDATE instead of DT_DBTIMESTAMP for your derived column?
July 17, 2008 at 10:25 pm
yes i did tried using DT_DBDATE . still i got the same error.
any ideas please?
July 18, 2008 at 5:46 am
July 18, 2008 at 5:50 am
Thanks Venkat. it works !!!!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply