November 12, 2015 at 7:11 am
I have date format coming from csv file like - "03/05/2015 15:19:56.727000"
How do I convert it into datetime2 or datetime in SSIS
I tried DBTIMESTAMP, DBTIMESTAMP2 but giving Error: Data conversion failed while converting column "datestr" (15) to column "Copy of datestr" (6). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
Please help. Its urgent!
Abhijit - http://abhijitmore.wordpress.com
November 12, 2015 at 9:02 am
Hi Abhijit
What is dateformat set on your server?
I 've got YYYY-MM-DD for example, so I simply do some setps:
1. Set up DT_STR, 26 as data type for column in flat file connection manager.
2. Add derived column with expression FINDSTRING([Your_column],"/",1) == 0 ? NULL(DT_STR,26) : ((DT_STR,26)(SUBSTRING([Your_column],7,4) + "-" + SUBSTRING([Your_column],4,2) + "-" + SUBSTRING([Your_column],1,2) + SUBSTRING([Your_column],11,16)))3. Set up the data conversion to DT_DBTIMESTAMP2 (Scale 6).
At the end you need put it into destination table.
Best regards,
Mike
November 12, 2015 at 2:35 pm
Look at the size of the string field in your flat file connection. If it's greater than 23 then SSIS will 'helpfully' tell you you're going to truncate your data.
I'm not 100% sure this is the issue but it's where I would start looking.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply