Post reply

Flat File Date Conversion

  • April 27, 2007 at 9:34 am

    #153553

    hi guys

    I've got a flatfile with 2 date fields which have the ISO format of yyyymmdd

    And i'd like to import it into my db as mmddyyyy.

    The fields are fixed length with 8 bytes.

    In my package if I try to set these 2 fields to a SSIS datatypes of

    Database_Date or DT_DATE and i get an error:

    [Toll_Log [65]] Error: Data conversion failed. The data conversion for column "TransactionDate" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

    I even tried passing it as string and then converting it to date with data convertion tool. and i get this error:

    [Data Conversion [1490]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "output column "Copy of TransactionDate" (1504)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "Copy of TransactionDate" (1504)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.

    How can i convert it properly in SSIS?

    Thanks

     

     

    Alex S

  • April 27, 2007 at 11:58 am

    #703023

    Instead of Database_Date or DT_DATE try using DT_DBTIMESTAMP.

  • April 27, 2007 at 12:05 pm

    #703025

    Lynn

    I get the same error.

    [TollLog [65]] Error: Data conversion failed. The data conversion for column "TransactionDate" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

    Alex S

  • April 27, 2007 at 1:10 pm

    #703048

    The field you are going to that you want formatted mmddyyyy, is this a date field or a character field?

  • April 27, 2007 at 2:07 pm

    #703053

    FINALLY!!!

    Lynn i changed those columns to (DT_STR) in the flat file source and then added derived column tool.

    I got it to work by using a derived column transformation tool and by adding

    (DT_DBDATE)(SUBSTRING([TranDate],1,4) + "/" + SUBSTRING([TranDate],5,2) + "/" + SUBSTRING([TranDate],7,2))

    in the expression box.

    In the table those 2 flelds are datatime.

    Converts yyyymmdd to mm/dd/yyyy.

     

     

    Alex S

  • April 27, 2007 at 2:28 pm

    #703056

    Curious, why are you storing dates as strings instead of as dates?

     

  • April 27, 2007 at 3:21 pm

    #703064

    They are stored as smalldatetime in the table after SSIS processing.

    But when they come in from flat file source i have to store them in string(DT_STR) in order to successfully convert from yyyy/mm/dd to mm/dd/yyyy.

    When i tried storing them as DT_DATE, DT_DBDATE or timestamp i got errors. 

     

     

    Alex S

  • October 30, 2007 at 8:37 am

    #746665

    Derived column and/or data type conversion works but its slow. I made a test and it takes 9 hours longer to import 30GB file using derived column than 1 to 1 mapping and implicit conversion (i skipped date field and run test on the same machine).

    I would really like to know the way how to configure date format and use the most simple and fastest load mechanism. I would like to have source flat file and destination table only plus 1:1 transformation in between. In Oracle and even MySQL its possible. Must be also possible in IS

    A Nobel price for the one who knows the answer!!

    Tomek

  • February 13, 2008 at 2:30 am

    #777721

    I got the same error as well. Did you got a solution yet? thanks 😀

  • September 17, 2008 at 7:08 am

    #872981

    I am having the same issues. Does anyone know an efficient way to convert dates?

  • September 18, 2008 at 5:12 am

    #873499

    Use ISO-Formated (YYYY-MM-DD) date-fields to import.

  • September 18, 2008 at 7:48 am

    #873623

    The files I recieve are usually in yyyymmdd or mm/dd/yyyy form. Unless I go in a change them myself, which I am not about to do, I have to import them like this somehow.

    Everything I have tried has failed on these imports. I have tried Cast and Convert statements with no luck either.

  • September 24, 2008 at 10:54 am

    #876534

    The SSIS Datatype Conversion functions DT_DATE, DT_DBDATE, DT_DBTIMESTAMP don't do what people expect them to do and there is a lot of confusion in this area (no thanks in part to BOL).

    We would expect that by applying the function before a datetime value it would convert it but as the input is in string format (which it usually is when dealing with flat file sources) it is necessary to convert the string into the exact format the datatime conversion function is expecting.

    Thus you will need to use the SUBSTRING function within an expression.

    DT_DBDATE also strips out the time portion, thus expects yyyy-mmm-dd or yyyy-mm-dd, so you need to build you datetime value in string format as yyyy-mmm-dd or yyyy-mm-dd.

    For extracting both date and time use DT_DATE which expects it as dd/mmm/yyyy hh:mm:ss or dd/mm/yyyy hh:mm:ss (this format is dependent upon the locale property of the Transformation but this is the format for English(UK).

    You also need to watch out for nulls and blank spaces in the source data which will cause the transformation to fail if not accounted for in your expression.

    Paul R Williams.

  • September 25, 2008 at 10:55 am

    #877183

    This comes handy:

    http://sqlblogcasts.com/blogs/jorg/archive/2008/06/12/ssis-convert-different-string-date-formats-to-datetime-with-the-script-task.aspx

    Alex S

  • September 25, 2008 at 11:53 am

    #877240

    Yea I actually posted something similar on the forums yesterday that I got resolved. Here is the code I had to use to get it to work properly.

    UPDATE temp_ameriflex_detail

    SET emp_doh = CONVERT(VARCHAR(10), CAST(emp_doh as DATETIME), 112)

    Thanks for the reply guys.

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

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