Post reply

SSIS - Problem with date format

  • December 21, 2011 at 11:35 pm

    #391187

    Hi,

    I have successfully read data from XML files to SQL Server tables. But dates are treated as following:

    XML file SQL Server table

    (dd/mm/yyyy) (yyyy-mm-dd 00:00:00.000)

    ------------- ----------------------------

    24/05/2006 2006-05-24 00:00:00.000

    01/04/2007 2007-01-04 00:00:00.000

    So, if day is more than 12, then date is stored correctly. But if date is less than or equal to 12, then day and month parts of the date get exchanged.

    I tried using Derived Column Transformation to fix this. The expression I used is:

    DAY([DateToBeConverted]) < 13 ? (DT_DATE)(SUBSTRING((DT_WSTR,25)[DateToBeConverted],1,4) + "-" + SUBSTRING((DT_WSTR,25)[DateToBeConverted],9,2) + "-" + SUBSTRING((DT_WSTR,25)[DateToBeConverted],6,2)) : [DateToBeConverted]

    But it is throwing this error:

    [Derived Column [422]] Error: The conditional operation failed.

    [Derived Column [422]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (422)" failed because error code 0xC0049063 occurred, and the error row disposition on "input column "AssetPriceDate" (802)" 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.

    Please help me in sorting this problem out!

    Thanks!

  • December 21, 2011 at 11:47 pm

    #1424593

    Do you have NULL values in your source?

    Try routing the error rows to a dummy destination and inspect them with a dataviewer. Maybe you'll notice something odd.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • December 22, 2011 at 12:24 am

    #1424598

    XML file SQL Server table

    (dd/mm/yyyy) (yyyy-mm-dd 00:00:00.000)

    ------------- ----------------------------

    24/05/2006 2006-05-24 00:00:00.000

    01/04/2007 2007-01-04 00:00:00.000

    SET DATEFORMAT dmy

    GO

    DECLARE @datevar datetime

    SET @datevar = '01/04/2007'

    SELECT @datevar

    GO

    Raunak J

  • December 22, 2011 at 12:54 am

    #1424607

    Thank you all for your reply!

    I modified the expression to fetch date directly from the XML file, rather than swapping mm and dd in SQL server table itself. This did the trick and it is working now.

Viewing 4 posts - 1 through 3 (of 3 total)

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