Dear all I am loading the data from Source (Oracle ) to Destination (Sql server 2008 R2) database Source Column date format is 1900-01-01 00:00:00.0000000 1900-01-01 00:00:00.0000000 1976-03-15 00:00:00.0000000 1900-01-01 00:00:00.0000000 1974-06-18 00:00:00.0000000 when i load this format into sql server using SSIS i got this error [OLE DB Destination [16]] Error: There was an error with input column "CUST_DOB" (1930) on input "OLE DB Destination Input" (29). The column status returned was: "Conversion failed because the data value overflowed the specified type.". i have changed my datetime format to datetime2(7) how to load the data without failure using ssis

Hi, Can you try using one of the casting functions in SSIS. Put a derived column and use a date time casting function, DT_DBDATE or DT_DBTIME etc. If it still doesn't work, use SELECT TO_CHAR([Your Column Here], 'YYYY-MM-DD HH24:MI:SS') and then try converting it into a Date type format. Regards, Vinaya

ssis can handle followign datetime values : DT_DBDATE yyyy-mm-dd DT_FILETIME yyyy-mm-dd hh:mm:ss:fff DT_DBTIME hh:mm:ss DT_DBTIME2 hh:mm:ss[.fffffff] DT_DBTIMESTAMP yyyy-mm-dd hh:mm:ss[.fff] DT_DBTIMESTAMP2 yyyy-mm-dd hh:mm:ss[.fffffff] DT_DBTIMESTAMPOFFSET yyyy-mm-dd hh:mm:ss[.fffffff] [{+|-} hh:mm] http://msdn.microsoft.com/en-us/library/ms141036.aspx accordingly you need to convert to anyone of these(as per your need) from the input data coming. you can try Data Transformation and there convert the input data from Oracle to into DT_DBTIMESTAMP or any thing using SUBSTRING(if inut column is in string). (DT_DATE)(SUBSTRING(column,1,4) + "-" + SUBSTRING(column,5,2) + "-" + SUBSTRING(column,7,4)) + " 00.00.00.000" or something like this. regards joon