Post reply

need to convert string to datetime datatype in DFT

  • October 21, 2015 at 10:39 am

    #309221

    Hi team,

    I have requirement that I need to insert data in datetime column in stage table which does not have any data from my flat file source. I am extracting that value from my source file name like source20151020.txt.

    so in DFT-> flatfile->derived transformation ( add new column, expression: substring(filename,7,8)) then I am trying to use data conversion to change from unicode string to db_timestamp. But it's failing with source code input column not supported for conversion.

    So please help me to make this conversion.

    I need to insert value: 20151020 in stage table column which is datetime datatype.

    Thank you,

  • October 21, 2015 at 4:15 pm

    #1834791

    Not sure about SSIS but it's mostly a breeze in T-SQL.

    SELECT CAST(CAST(20151020 AS CHAR(8)) AS DATETIME)

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 23, 2015 at 1:59 pm

    #1835312

    Hi Jeff,

    It's working in SQL but not suits in ssis.

    thanks,

  • October 23, 2015 at 2:03 pm

    #1835313

    team,

    I have tried with below code in "Execute SQL Task"

    Declare @FileDate datetime

    declare @filename varchar

    -- filename = test_source_file_20150920

    Set @FileDate = substring(@filename,11,8)

    select @FileDate

    but it's giving default value not from file name value. any help?

    Thanks,

  • October 24, 2015 at 4:13 pm

    #1835369

    rajbetl11 (10/23/2015)

    team,

    I have tried with below code in "Execute SQL Task"

    Declare @FileDate datetime

    declare @filename varchar

    -- filename = test_source_file_20150920

    Set @FileDate = substring(@filename,11,8)

    select @FileDate

    but it's giving default value not from file name value. any help?

    Thanks,

    With the filename line commented out, what did you expect?

    You need to pass a file name to the task and I have no idea how to do that. That's why I normally don't bother with SSIS and do all this stuff using T-SQL.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 5 (of 5 total)

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