SSIS - Problem with date format

Question

SSIS - Problem with date format

khushboo.dudani

SSChasing Mays

Points: 616
More actions
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!

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

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

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

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 2

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

khushboo.dudani SSChasing Mays Points: 616 More actions · Answer 3

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.