none
The value could not be converted because of a potential loss of data.

    Question

  • Hi,

    I have looked through a few similar threads but I couldn't find the solution to my exact problem. I have this date property in the format 12/12/2009  07:06:00 in a CSV file and upon importing I need to write it as a date into the database too which maps the property database timestamp [DT_DBTIMESTAMP] but I'm unable to write an expression that basically changes this 12/12/2009  07:06:00 value to be accepted by the database timestamp [DT_DBTIMESTAMP]. Any help would be appreciated again.

    Regards
    Haris

    Thursday, March 01, 2012 8:59 PM

Answers

  • A simple SUBSTRING(Pickup,1,19) did the work, thanks everyone.
    • Marked as answer by haris49 Sunday, March 04, 2012 3:56 PM
    Sunday, March 04, 2012 3:55 PM

All replies

  • you can reach it with a simple conversion:

    (DT_DBTIMESTAMP)[YourDateFieldName]


    http://www.rad.pasfu.com

    Thursday, March 01, 2012 9:14 PM
    Moderator
  • so you want to convert  database timestamp [DT_DBTIMESTAMP] to a sql server date data type right?

    This converted my input from DT_DBTIMESTAMP to SQL server date

    (DT_DBDATE)date

    • Edited by sql393 Thursday, March 01, 2012 9:25 PM
    Thursday, March 01, 2012 9:15 PM
  • you can reach it with a simple conversion:

    (DT_DBTIMESTAMP)[YourDateFieldName]


    http://www.rad.pasfu.com

    Thanks for the reply, I tried the suggested expression but it comes up with the following error:

    Error: 0xC0049064 at Data Flow Task, Derived Column [542]: An error occurred while attempting to perform a type cast.

    Thursday, March 01, 2012 10:17 PM
  • so you want to convert  database timestamp [DT_DBTIMESTAMP] to a sql server date data type right?

    This converted my input from DT_DBTIMESTAMP to SQL server date

    (DT_DBDATE)date

    Cheers for the reply but that's not what I intend to do. I want to convert the date 12/12/2009  07:06:00 which is in dt_str format to a [DT_DBTIMESTAMP].
    Thursday, March 01, 2012 10:19 PM
  • When I had a value of 00:00.0 in the csv file for another table, this (DT_DBDATE)"00:00.0" worked for me and it was accepted at the destination which was also in a [DT_DBTIMESTAMP] format.
    Thursday, March 01, 2012 10:21 PM
  • you can reach it with a simple conversion:

    (DT_DBTIMESTAMP)[YourDateFieldName]


    http://www.rad.pasfu.com

    Thanks for the reply, I tried the suggested expression but it comes up with the following error:

    Error: 0xC0049064 at Data Flow Task, Derived Column [542]: An error occurred while attempting to perform a type cast.

    I believe that this is a runtime error? am I right?

    if yes, you should configure error output for derived column and redirect error rows to a flat file destination.

    It seems that there are some values in your datefield which hasn't propert structure so they can't be converted to DT_DBTimeStamp , with configuring error output you will find them and fetch them out.


    http://www.rad.pasfu.com

    Thursday, March 01, 2012 10:32 PM
    Moderator
  • I have to configure an error output using a derived column and redirect the rows containing null data to a flat file destination anyway but this is meant to work using a long expression to convert it to a DT_DBTimeStamp to be accepted at the destination but I just can't figure out what is it going to be.
    Saturday, March 03, 2012 2:25 PM
  • Okay, i tried

    (DT_DBTIMESTAMP)(SUBSTRING(Pickup,6,2) + "/" + SUBSTRING(Pickup,9,2) + "/" + SUBSTRING(Pickup,1,4) + " " + SUBSTRING(Pickup,10,8))

    but it comes up with the error:

    Error: 0xC0049064 at Import Customer Loads Data, Change Date Format [542]: An error occurred while attempting to perform a type cast.
    Error: 0xC0209029 at Import Customer Loads Data, Change Date Format [542]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "component "Change Date Format" (542)" failed because error code 0xC0049064 occurred, and the error row disposition on "input column "Pickup" (610)" 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.
    Error: 0xC0047022 at Import Customer Loads Data, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Change Date Format" (542) failed with error code 0xC0209029 while processing input "Derived Column Input" (543). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.
    Information: 0x40043008 at Import Customer Loads Data, SSIS.Pipeline: Post Execute phase is beginning.
    Saturday, March 03, 2012 7:37 PM
  • A simple SUBSTRING(Pickup,1,19) did the work, thanks everyone.
    • Marked as answer by haris49 Sunday, March 04, 2012 3:56 PM
    Sunday, March 04, 2012 3:55 PM