locked
Importing DATE with Timestamp(In a Flat file) Column using SSIS RRS feed

  • Question

  • Hi

    SSIS is brand new for me.. Playing with since a few hours..

    Iam trying to import a Flat File into the SQLSERV DB using SSIS..
    One of the column is in this format -- "YYYYMMDDHH24MISS"

    How do i get around this to import the data in a readable fashion into the Destination?

    Thanks!
    MKR


    Wednesday, April 18, 2007 8:45 PM

Answers

  • Hi MKR,

     

    What data type are you wanting the result to be?

    You can use a derived column component to parse the format of the column and create anything you like -- a DT_DBTIMESTAMP, a string with your own format, etc...

     

    You could turn the string in the format you have above into a string with this format:  "YYYY-MM-DD HH:MM: SS" with an expression like this in derived column (where i am assuming the string is in a column called 'Col'):

     

    SUBSTRING(Col, 1, 4) + "-" + SUBSTRING(Col, 5,2) + "-" + SUBSTRING(Col, 7,2) + " " + SUBSTRING(Col, 9,2)  + ":" + SUBSTRING(Col, 13,2) + ":" + SUBSTRING(Col, 15,2)

     

    Is that the sort of thing you are looking for?

     

    Thanks
    Mark

    Wednesday, April 18, 2007 9:00 PM

All replies

  • Hi MKR,

     

    What data type are you wanting the result to be?

    You can use a derived column component to parse the format of the column and create anything you like -- a DT_DBTIMESTAMP, a string with your own format, etc...

     

    You could turn the string in the format you have above into a string with this format:  "YYYY-MM-DD HH:MM: SS" with an expression like this in derived column (where i am assuming the string is in a column called 'Col'):

     

    SUBSTRING(Col, 1, 4) + "-" + SUBSTRING(Col, 5,2) + "-" + SUBSTRING(Col, 7,2) + " " + SUBSTRING(Col, 9,2)  + ":" + SUBSTRING(Col, 13,2) + ":" + SUBSTRING(Col, 15,2)

     

    Is that the sort of thing you are looking for?

     

    Thanks
    Mark

    Wednesday, April 18, 2007 9:00 PM
  • Thanks Mark..

    But as i was telling you earlier.. My Knowledge on SSIS is very limited..
    Now that i know we can manipulate the string..

    Where do i do this -- I mean, where do i add this SUBSTRING Manipulation..


    Wednesday, April 18, 2007 9:16 PM
  • You want to add in in the data flow, using a derived column transformation.
    Wednesday, April 18, 2007 9:21 PM
  • Thanks! Welch n Mark

    Wednesday, April 18, 2007 9:25 PM
  •  

    And for future reference, if you have a date like this: 12/31/2007 10:12:14.123

    i.e. MM/DD/YYYY HH:MMTongue TiedS.ttt

     

    The code is:-

    (DT_DBTIMESTAMP)(SUBSTRING(DateTime,7,4) + "-" + SUBSTRING(DateTime,1,2) + "-" + SUBSTRING(DateTime,4,2) + " " + SUBSTRING(DateTime,12,12))

    Tuesday, October 30, 2007 11:05 PM