May 1, 2009 at 9:08 am
Hi,
I need help for conversion of datatypes in DTS pack. I am using a flat file which has date field as string. I am loading this file to database which contains columns with 'datetime' datatype. Any help will be appreciated!!!
May 1, 2009 at 8:29 pm
What is the format of the dates in the source file? Please post an example.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 3, 2009 at 8:52 am
It's a csv file.
"123456432",0,0,0,0,"3214","21/04/2009"
The second value in the file is a date field. This file is automatically generated from prog with lots of other information including dates. Some fields have dates and some don't. Null string date type field is giving problem while loading data to datetime column
May 3, 2009 at 5:56 pm
When you say the second value, you mean the one which is zero in your sample data? Zero is different from null and needs to be handled differently.
If the value is zero, do you want to import it as null?
Or did you mean 'seventh' rather than 'second'?
Phil
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 4, 2009 at 7:19 am
Yes it is 0 in sample data. Sometimes it is an empty field e.g 123456,,0,12/04/09etc
May 4, 2009 at 7:32 am
I just confirmed thius file is automatically generated from prog and will proper date field or nothing which is blank field
May 4, 2009 at 11:39 am
I am getting following error:
The number of failing rows exceeds the maximum specified:
Transformcopy'DTSTransformation_95'conversion error:Conversion invalid for datatypes on column pair1(source column'Col095'(DBTYPE_STR), destination column'createDate'(DBTYPE_DBTIMESTAMP)).
May 4, 2009 at 2:01 pm
I assume your already using a transform task to cast it into a date.
If not add a derived column transform between your source and your destination.
for Col095 Replace Col095, and put this in your expression.
ISNULL(Col095) || TRIM(Col095) == "" ? NULL(DT_DATE) : (DT_DATE)Col095
If Col095 is null, OR "" then NULL(DT_Date) else Cast(Col095) As Date.
If your column does not allow nulls, then replace NULL(DT_Date) with (DT_Date)'01/01/1900' or whatever default date you want
May 4, 2009 at 2:19 pm
Thanks for your reply. But i am new to this DTS programming. Also I ahve little knowledge about VB script
' Copy each source column to the destination column
Function Main()
Main = DTSTransformStat_OK
End Function
Function TransformFailureMain()
DTSDestination("CreateDate") = CDate(DTSSource("Col095"))
TransformFailureMain = DTSTransformStat_OK
End Function
Function InsertSuccessMain()
DTSDestination("RNS") = CDate(DTSSource("Col066"))
InsertSuccessMain = DTSTransformStat_OK
End Function
This is the scrpit I am using to load this file. This script is working and loading data. But when I query to date field is returns nothing
May 4, 2009 at 4:59 pm
It seems that you need to modify the script, which looks curiously like a VBScript DTS script.
Try replacing this line
DTSDestination("CreateDate") = CDate(DTSSource("Col095"))
With this
If IsDate(DTSSource("Col095")) Then
DTSDestination("CreateDate") = CDate(DTSSource("Col095"))
End If
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 7, 2009 at 12:56 pm
Now I ahve encounter another problem. My DTS pack is working fine. But now it is not loading proper data.
eg my csv file contains 108 109 110 111112 113114
1-0697893 04/09/2009 18.5 0 0.93
It is loading col108 with nothing evenif in csv file it has data.
col109 with 30/12/1899 evenif csv has valid date
col112,113and col114 with NULL values. (These values in csv do contain some null values)
Please help!!
May 7, 2009 at 6:19 pm
Your sample data contains no commas, so each row will be interpreted as a single field.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply