derieved column string to date conversion

Question

Hi all,
I am trying to convert column like 23/01/2007 to date format first and then casdt conversion but I dont know why is it still failing
(DT_DBDATE)((SUBSTRING([Due Date],7,4)) + "-" + (SUBSTRING([Due Date],4,2)) + "-" + (SUBSTRING([Due Date],1,4)))
its not working for me. I have also tried adding '00:00:00'.
Can any oneplease helo me out with it?

Answer 1

Check my blog.

Ur expression is correct except:
(DT_DBTIMESTAMP)((SUBSTRING([Due Date],7,4)) + "-" + (SUBSTRING([Due Date],4,2)) + "-" + (SUBSTRING([Due Date],1,2)))

---

Hope this helps !!
Sudeep Raj   |    My Blog |    SSIS Samples

Answer 2

Try:

(DT_DATE)((SUBSTRING( [DueDate] ,7,4)) + "-" + (SUBSTRING([DueDate],4,2)) + "-" + (SUBSTRING([DueDate],1,2)))

---

http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Answer 3

Hi,

i actually tried the same expression earlier and have tried it again. (SUBSTRING([DueDate],1,4))) in my origional post was probebly a mistype but the problem is that it soen't work. I get the errro message,

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

Error: 0xC0209029 at Data Flow Task, Derived Column [1280]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (1280)" failed because error code 0xC0049064 occurred, and the error row disposition on "output column "date" (2066)" 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 Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Derived Column" (1280) failed with error code 0xC0209029. 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.

Again the column formet is 31/03/2010. Expresion actually looked fine to me but doesnt work. It will be great if some one could help me out with.

Thanks

Answer 4

I can only assume that you have some erroneous data. Use the Error output from the derived column and put a dataviewer on it to see the data that is failing the expression.

-Jamie

---

http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Answer 5

Are you sure the date column has correct values? I mean the value in date column should be dd/mm/yyyy format.

---

Nitesh Rai- Please mark the post as answered if it answers your question

Answer 6

Jamie has the reight option for you. configure the Error output put a data viewer to check the eroneous records.
You will see that some data is not in the expected format, maybe blanks or incorrect date etc.

---

Hope this helps !!
Sudeep Raj   |    My Blog |    SSIS Samples

Answer 7

hi,

WHat I discovered now is that SSIS ir presenting data 31/03/2009 as 2009-03-31. May be treeeting it as date? But in flat file connection I have declared it as string
anyways I have tried (DT_DATE)((SUBSTRING( [DueDate] ,1,4)) + "-" + (SUBSTRING([DueDate],6,2)) + "-" + (SUBSTRING([DueDate],9,2)))  but still get the same error. What I am sure about it that there isnt anything wrong with the expression as it gets highlited otherwise.
Data is definaly consistent as I am not inporting much yet. I have checked that.

Answer 8

how and where are you checking the data as yyyy-mm-dd?

Have you put a data viewer before the derived column and for error outputs from the derived column.
Also put a data viewer after the derived column(successful records).
Let us know ur observation.

---

Hope this helps !!
Sudeep Raj   |    My Blog |    SSIS Samples

Answer 9

thanks mate. Data viewer helped. Basically there was a comma in one of the fields which was making other fields shift one column and hance wrong data in date column. :/
But ya expression is right.

Answer 10

Please mark the appropriate thread(s) as answer.

---

Hope this helps !!
Sudeep Raj   |    My Blog |    SSIS Samples