Varchar to Timestamp

[Nav_DS]

Hi Folks,

I've been working to solve two issues, but haven't been successful as yet. So, decided to lay down the issue here to see if anyone can help me:

1)
ETL Map Source : Flat-File
ETL Map Target : SQL Server 2005 Table

There's a field in the flat file with values similar to -> 9/28/2007 (mm/dd/yyyy) without any time component, which will get mapped to a field in my target table defined using the Datetime datatype with values similar to -> 2007-09-28 00:00:00.000 (Timestamp)

I've been facing problems in the transformer stage trying to convert the incoming Varchar field into a Datetime field!

Logs show a warning stating that the conversion function to convert to a timestamp_from_string is not working.

I used a PEEK to see the contents of this field and all i can see is -> SRV_Date: **********

Can anyone help me resolve this issue ?

2)Within the same FlatFile is an Amount field with values similar to -> $330.00 which will get mapped to an Amount field in the target table defined as a Decimal (19,2)
Effort is to remove the prefixed - '$' symbol before loading the table. I tried using StringToDecimal but this didn't work !

For both the issues above, can anyone give me the right SYNTAX as in examples so that i can get these hurdles out of the way ASAP !

[mandyli]

)
ETL Map Source : Flat-File
ETL Map Target : SQL Server 2005 Table

There's a field in the flat file with values similar to -> 9/28/2007 (mm/dd/yyyy) without any time component, which will get mapped to a field in my target table defined using the Datetime datatype with values similar to -> 2007-09-28 00:00:00.000 (Timestamp)

I've been facing problems in the transformer stage trying to convert the incoming Varchar field into a Datetime field!

Logs show a warning stating that the conversion function to convert to a timestamp_from_string is not working.


: - This one please don't use conversion here. try to load Varchar to TimeTime also you need to load without last 3 '.000'

2)Within the same FlatFile is an Amount field with values similar to -> $330.00 which will get mapped to an Amount field in the target table defined as a Decimal (19,2)
Effort is to remove the prefixed - '$' symbol before loading the table. I tried using StringToDecimal but this didn't work !


Decimal will not accept $ symbol. So you need load amount value only.

Thanks
Man

[DSguru2B]

Append time to you date with a space between date and time and then use StringToTimestamp() to convert into a timestamp.
As for your other issue, use Convert() to convert dollar sign to an empty character('').

[sandeepgs]

Hi,

Question 1: Convertion of varchar EX: 9/28/2007 to 2007-09-28 00:00:00.000

Solution: As your reading this as varchar in transformer you can do the convertion. I am specifying the logic

StringToTimestamp(DSLink8.a :"000000","%mm/%dd/%yyyy%hh%nn%ss")

DSLink8.a is the input column name.Specify your column name here.

This conversion specified will convert the varchar to timestamp.

Question 2: Convert the amount in the source (flat file) Ex: $330.00 to a decimal value in the target O/P should be 330.00

Solution: when you are reading as varchar in source then use this conversion function in transformer. It will convert varchar to decimal.

StringToDecimal(DSLink8.s [2,19],"ceil")

Here in this derivation I uses a "substr" function and specified the starting position as "2"

DSLink8.s is the column name.Specify your column name here.



Try this out If any questions please let me know..

[Nav_DS]

Thanks guys for pitchin in quick !

DSguru2B's example is what solved the issue for me...

Timestamp:

I isolated Day Month & Year into 3 seperate stage variables and then appended each of them in the format i needed along with the timestamp aswell. i.e : SV_YEAR:'-':SV_MON:'-':SV_DT:' ':' 00:00:00.000' which will output 2007-12-22 00:00:00.000 !

The second issue was resolved using the CONVERT function :
TRIM(CONVERT("$"," ",Amount_Field))

Thanks again gentlemen for your quick responses