Convert String DataType to Date datatype in DTS

  • 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!!!

  • 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.

  • 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

  • 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.

  • Yes it is 0 in sample data. Sometimes it is an empty field e.g 123456,,0,12/04/09etc

  • I just confirmed thius file is automatically generated from prog and will proper date field or nothing which is blank field

  • 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)).

  • 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

  • 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

  • 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.

  • 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!!

  • 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