none
Trying to add date + time in SSIS

    Question

  • I have an old Foxpro table that has a date and a time,  the time is basically string.

    I found a few threads that discuss this, however I'm getting an error (see attached).

    http://social.msdn.microsoft.com/Forums/en-US/07407851-d8a2-4a2b-8b4b-791d8d0d7cb6/how-to-combine-date-and-time-column-types-into-one-datetime-column-type-using-sql-server-2012?forum=sqlintegrationservices

    I have an old Foxpro table that has a date and a time,  the time is basically string.

    I can convert the date to a db_date no problem but:

    When I try to add a derived column using

     (DT_DBTIMESTAMP)((DT_STR,10,1252)date+" "+(DT_STR,162)time) 

    It returns 

    TITLE: Microsoft Visual Studio
    ------------------------------

    Error at ORDR_CMT, PO, POD [Derived Column [778]]: Attempt to parse the expression " (DT_DBTIMESTAMP)((DT_STR,10,1252)date+" "+(DT_STR,162)time) " failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.

    Error at ORDR_CMT, PO, POD [Derived Column [778]]: Cannot parse the expression " (DT_DBTIMESTAMP)((DT_STR,10,1252)date+" "+(DT_STR,162)time) ". The expression was not valid, or there is an out-of-memory error.

    Error at ORDR_CMT, PO, POD [Derived Column [778]]: The expression " (DT_DBTIMESTAMP)((DT_STR,10,1252)date+" "+(DT_STR,162)time) " on "output column "Date2" (788)" is not valid.

    Error at ORDR_CMT, PO, POD [Derived Column [778]]: Failed to set property "Expression" on "output column "Date2" (788)".



    ------------------------------
    ADDITIONAL INFORMATION:

    Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)

    -- 

    http://social.msdn.microsoft.com/Forums/en-US/07407851-d8a2-4a2b-8b4b-791d8d0d7cb6/how-to-combine-date-and-time-column-types-into-one-datetime-column-type-using-sql-server-2012?forum=sqlintegrationservices

    Wednesday, November 06, 2013 9:49 PM

All replies

  • (DT_DBTIMESTAMP)((DT_STR,10,1252)date+" "+(DT_STR,162)time)?

    why so different numbers for length/codepage?

    Try

    (DT_DBTIMESTAMP)((DT_STR,10,1252)date+" "+(DT_STR,8,1252)time) 


    Arthur My Blog

    Wednesday, November 06, 2013 9:54 PM
  • Nope, same error... Just copied dt_str from another sample, didn't know what the params were.
    Thursday, November 07, 2013 2:04 AM
  • please provide what data causes the issue

    Arthur My Blog

    Thursday, November 07, 2013 3:12 PM
  • Hi Syncro,

    The error message generally indicates that the syntax of the expression in the Derived Column is incorrect. From the error message, we can see the (DT_STR,162) part which is an invalid data type. As Arthur posted, we should modify the expression as follows:

    (DT_DBTIMESTAMP)((DT_STR,10,1252)date+" "+(DT_STR,8,1252)time)

    I also test the above expression and it works well. Please see the following screenshot:

    So, please make sure you have copy or write the above expression correctly. If the issue persists, please post the error message.

    Regards,
    Mike Yin

    If you have any feedback on our support, please click here


    Mike Yin
    TechNet Community Support

    Monday, November 11, 2013 3:32 PM
  • Hi Mark,

       Thanks for the answer,  I copied and pasted exactly,

    I do not know how to get to the OLE Source Ouput Viewer.

    I saved and tried to replace the existing date,

    When I ran it and it seemed to take however I get this 

    [Derived Column [778]] Error: The "component "Derived Column" (778)" failed because truncation occurred, and the truncation row disposition on "input column "date" (786)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

    I tried to add a new field (date2) as timestamp,  I could not change the type of the derived date field

    However I'm now getting.

    [Derived Column [778]] Error: The "component "Derived Column" (778)" failed because truncation occurred, and the truncation row disposition on "output column "date2" (886)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

    Thanks Again.

    Monday, November 11, 2013 7:14 PM
  • I don't know if its related, but hardly any of the tables I'm migrating have the code page set.

    Almost always I edit columns in the OLE db source editor, I get this message.  Is it related, can I turn it off?

    The component reported the following warnings:

    Warning at {5DF52DC3-BC17-4542-B50D-BAB2AF01D4A2} [Source 41 - ordr_cmt [271]]: Cannot retrieve the column code page info from the OLE DB provider.  If the component supports the "DefaultCodePage" property, the code page from that property will be used.  Change the value of the property if the current string code page values are incorrect.  If the component does not support the property, the code page from the component's locale ID will be used.

     

    Monday, November 11, 2013 7:19 PM
  • What is the source database?

    See whether http://msdn.microsoft.com/en-us/library/gg164830.aspx applies to your case.


    Arthur My Blog

    Monday, November 11, 2013 8:21 PM