Date Conversion in ssis

  • Hi All,

    I have a question in date conversion is ssis. I am newbie in ssis. My task is, do insert the data to sql table from the flat file source. i have a datetime field in that flatfile. I need to insert that field as datepart. How do i do this. I dont know how to convert the datetime to datepart. Whether is this possible?

    Thanks in advance

    prasanna

  • Please post a sample of the file.

    May your fish always be bigger
    Than the holes on your net.

  • You may be able to use the Data Conversion transformation to write an expression that gets you the datepart of the date. Or failing that, how strong is your .NET? Has you could use the Script Component transformation to write your own function that does this for you.

  • Sorry i'm not well in .Net. Could you help me to solve this issue.

  • Unless you post sample data, as ruizeddy already requested above, no one will be able to give you detailed help.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • id name datefield

    1001 USA 2004-09-03 12:00:18

    1002 SIN 2004-05-03 11:00:18

    1003 CAN 2004-03-05 11:06:27

    1004 HONG 2004-08-06 02:03:58

    1005 IND 2004-09-07 09:00:29

    Now i would like to convert the date format into

    datefield

    Sep 03 2004

    May 03 2004

    March 03 2004

    August 06 2004

    Sep 07 2004

    could anyone help me to resolve this?

  • If your flat file date format is already in the format

    YYYY-MM-DD HH:MM

    it is in perfect shape for loading direct into a SQL Server datetime field and you should be able to stuff it straight in there, losing the time component en route, as the following code shows:

    declare @strDate as varchar(20), @Date as datetime

    Set @strDate = '2004-09-03 12:00:18'

    Set @Date = Left(@strDate, 11) + '00:00:00.000'

    Select @strDate [StringDate], @Date [Date]

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Where do i put this code? whether data conversion or derived column please let me clear

  • Derived column works well.

    Create a new column with a formula like this:

    [SourceTextDateTimeField]

    and give it a datatype of DT_DBDATE. This casts the string to a date and strips away the time component all in one go.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Another option is to execute the data flow task without any conversions, then execute an update afterwards, to update the data as such:

    update MYTABLE set [DATE_FIELD] = CONVERT(VARCHAR(12), [DATE_FIELD], 107)

    This would work, if the data type for the date field is not a date type.

    May your fish always be bigger
    Than the holes on your net.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply