Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 Convert Date from Excel Source

Author  Topic 

brubakerbr
Yak Posting Veteran

53 Posts

Posted - 2013-12-18 : 09:53:42
I am working on automating some data that I receive from Germany. The Date format comes in as DD.MM.YYYY and I need it to be MM/DD/YYYY.

I am building an import package using SSIS and I added a derived column to change the date format.

I first tried to use

(DT_DATE) [CalendarDay]

but I keep getting an error at the Derived Column when I execute the package.

[Derived Column [2]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.
The "Derived Column" failed because error code 0xC0049064 occurred, and the error
row disposition on "Derived Column.Outputs[Derived Column Output].Columns[Date]"
specifies failure on error.

So I moved through the many examples in StackExchange (all that I could find at least) and was met with the same error or not the desired output.

Any suggestions.

Brian D. Brubaker
Business Intelligence Analyst
Viega LLC

brubakerbr
Yak Posting Veteran

53 Posts

Posted - 2013-12-18 : 10:09:56
I fixed the error by using the Substring Method that was provided by user3112708 and making an minor alteration to the numbering sequence.

SUBSTRING(CalendarDay,4,2) + "/" + SUBSTRING(CalendarDay,1,2) + "/" +
SUBSTRING(CalendarDay,7,4)

Thank you every one for your help.


Brian D. Brubaker
Business Intelligence Analyst
Viega LLC
Go to Top of Page
   

- Advertisement -