Hi, I am trying to extarct the string column data from a falt file which has date of format ddmmyyyy and some null values in it i am tyring to load that column into sql server using SSIS derived Column expression into data type of YYYY-mm-DD 00;00;00;00000 LEN(TRIM(DNADATE)) == 0 ? NULL(DT_DATE) : (DT_DATE)(SUBSTRING(DNADATE,5,4) + "-" + SUBSTRING(DNADATE,3,2) + "-" + SUBSTRING(DNADATE,1,2)) i am getting an errors mess like this [OLE DB Destination [9]] Error: There was an error with input column "DNADATE" (646) on input "OLE DB Destination Input" (22). The column status returned was: "Conversion failed because the data value overflowed the specified type.". CAn anyone help me in solving this problem pls Kind reagrds Ravilla

Your expression is correct. I tried with same expression. It worked for me. Try the following one. LEN(TRIM(DNADATE)) == 0 ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)(SUBSTRING(DNADATE,5,4) + "-" + SUBSTRING(DNADATE,3,2) + "-" + SUBSTRING(DNADATE,1,2))

The problem is not with the conversion but the data type column in the destination table. The conversion statement would nicely convert your date string into DT_DATE but when you have mapped it to a destination column it fails to fit in the data and hence the overflow error. Can you check the data type of the destination column!~V

HI The problem is not with the conversion but the data type column in the destination table. The conversion statement would nicely convert your date string into DT_DATE but when you have mapped it to a destination column it fails to fit in the data and hence the overflow error. Can you check the data type of the destination column! My destinatio column datatype is datetime Kind regards Ravilla

I feel its not an issue with the above. Its seems to me that you have a data issue. Can you use redirect error records while inserting the record to DB and check the column value. Could be that you have an invalid date coming or something similar.My Blog | Ask Me | SSIS Basics

Yeah sudeep is right, and even you can try having an data conversion component abouve destination and try converting it to datetime befor inserting. This will be of no use, because in the expression that is being set, the value is already being calculated as dateTime. So all the error records will be removed here.My Blog | Ask Me | SSIS Basics

Hi sudeep, My column has values with invalid dates like(15920211 and 15980611), and correctdates(11012012),some nulll values as well. can you help me in writing the SSIS expression to load into SQL Please Kind reagrds Ravilla

SQL Server accepts date range from January 1, 1753, through December 31, 9999. First check your date if it is greater than 1753. If it is then convert to datetime else null that value.

Hi sudeep, My column has values with invalid dates like(15920211 and 15980611), and correctdates(11012012),some nulll values as well. can you help me in writing the SSIS expression to load into SQL Please Kind reagrds Ravilla No need to change the expression. In the drived column click "Configure Error Output" Then select the redirect row for error for the new date column.My Blog | Ask Me | SSIS Basics

Hi SQL inSANE thanks for your help the code LEN(TRIM(DNADATE)) == 0 || SUBSTRING(TRIM(DNADATE),5,4) <= "1753" ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)(SUBSTRING(DNADATE,5,4) + "-" + SUBSTRING(DNADATE,3,2) + "-" + SUBSTRING(DNADATE,1,2)) has worked. I got a another date column which has proper dates like(01102011) but i having null values and blank values in it ISNULL (Opdate_01) || TRIM(Opdate_01)=="" ? "NULL" : DT_DATE)(SUBSTRING(Opdate_01,5,4) + "-" + SUBSTRING(Opdate_01,3,2) + "-" + SUBSTRING(Opdate_01,1,2)) i am trying to execute the above one but it is not working can you help in this scenario pls. Kind regards Ravilla

same code should work LEN(TRIM(Opdate_01)) == 0 || SUBSTRING(TRIM(Opdate_01),5,4) <= "1753" ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)(SUBSTRING(Opdate_01,5,4) + "-" + SUBSTRING(Opdate_01,3,2) + "-" + SUBSTRING(Opdate_01,1,2))