Results 1 to 5 of 5

Thread: Loading data into tera data TIME(0)

  1. #1
    SongForWhoever is offline Junior Member
    Join Date
    Apr 2013
    Location
    Manchester, UK
    Posts
    3
    Rep Power
    0

    Loading data into tera data TIME(0)

    Hi all - first post!

    I'm attempting to load data into a TIME(0) column on teradata, however attunity in SSIS is seeing the column as DT_R8 instead of DT_STR.

    TIME(1)....TIME(2) etc is interpreted as DT_STR! But I'm having no look with TIME(0).

    i have attempted to data convert to DT_R8 to load onto TIME(0) and whilst the component validates it fails when built and run.

    any ideas as to what is happening here??

  2. #2
    Hein is offline Senior Member
    Join Date
    Dec 2007
    Location
    Nashua, NH - USA.
    Posts
    165
    Rep Power
    13
    >> Hi all - first post!
    Welcome!
    Please consider creating a fresh username which is a little more real, perhaps with
    at least a first name for us to use, or sign off with a name, even if totally made up.
    There is very little incentive to expand any effort to please a nameless blob.
    In fact... i almost whacked the whole post as 'obvious spam' due to the name.

    Anyway, I know nothing much, but suspect a few more details would be welcome...

    >> load data into a TIME(0) column on teradata,
    So that's hh:mm:ss, no fractional seconds right?
    So I think yo want DT_DBTIME on the output?

    >> i have attempted to data convert to DT_R8.
    How exactly?And why? DT_R8 is a floating point right? How should the map to a time?
    Integration Services Data Types
    What exactly is this input source/fomat?

    >> it fails when built and run.
    How exactly? Invalid assignment? Date Time field overflow?

    Perhaps you need to use a [derived column] tool, and add a column with one (or more) CAST expressions?
    You may indeed have to go through DT_STR to to snip off fractional seconds.

    Hope this helps a little,
    Hein
    Last edited by Hein; 04-11-2013 at 07:46 PM. Reason: Forgot to reply to 'first post'

  3. #3
    SongForWhoever is offline Junior Member
    Join Date
    Apr 2013
    Location
    Manchester, UK
    Posts
    3
    Rep Power
    0
    Thanks hein - feedback taken onboard.
    I shall re-iterate my issue and try to give some more context around what is happening.


    What Works:
    I have an empty teradata table with a column formatted as "TIME(1)", example format: 14:15:21.0 (1 decimal precision).
    In SSIS I have an OLE DB Data Source which executes the following SQL, "SELECT '14:15:21' as Test_Time" to return >>> '14:15:21'
    The data flow path that comes out of the OLE DB Data Source shows the metadata of "Test_Time" to be in a format of "DT_STR. The data flow path flows into an attunity Teradata Destination. "Test_Time" is mapped to my teradata table column "Test_Time".

    Advanced properties of the Attunity component shows that the external teradata column "Test_Time" is interpreted as DT_STR, which is correct.

    1 Row is successfully inserted into Test_Time on Teradata >> Result: 14:15:21.0


    What I want, but doen't works:
    I have an empty teradata table with a column formatted as "TIME(0)", example format: 14:15:21 (no precision).
    In SSIS I have an OLE DB Data Source which executes the following SQL, "SELECT '14:15:21' as Test_Time" to return >>> '14:15:21'
    The data flow path that comes out of the OLE DB Data Source shows the metadata of "Test_Time" to be in a format of "DT_STR. The data flow path flows into an attunity Teradata Destination. "Test_Time" is mapped to my teradata table column "Test_Time".

    Advanced properties of the Attunity component (Input and Output properties >> Teradata Destination Input >> External Columns) shows that the external teradata column "Test_Time" is interpreted as data type DT_R8, which is incorrect. Why is this not DT_STR as with TIME(1)????

    The teradata attunity component does not validate with the following error: Data Type conversion of input column "Test Time" (219) is not supported.

    I hope this helps.
    Joe.

  4. #4
    Hein is offline Senior Member
    Join Date
    Dec 2007
    Location
    Nashua, NH - USA.
    Posts
    165
    Rep Power
    13
    Thanks for the clarification Joe!

    Sounds like a bug to me, one that you cannot get around.

    You may want to open up a support case for this, probably with Microsoft, possibly Teradata.
    I don't think there is any Attunity code in the path where this goes wrong, but I'll be gladly proven wrong.

    fwiw, I don't have a Teradata target handy here, and used SQLserver for a quick test.
    With SQLserver, both time(1) and time(0) target show up as DT_DBTIME in the extarnal column properties of the target.
    SSIS to SQL Server Data Type Translations


    hth,
    Hein.
    Last edited by Hein; 04-12-2013 at 08:40 AM.

  5. #5
    SongForWhoever is offline Junior Member
    Join Date
    Apr 2013
    Location
    Manchester, UK
    Posts
    3
    Rep Power
    0
    Thanks Hein,
    I'll let you know what the outcome is! :)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •