HEllo friends I am migration oracle tables to sql using SSIS. While doing this i am trying to use Derived column transfrom to convert Date format in oracle to sql. But i do not have any clue that how should i use this. In oracle side date is in mm/dd/yy format and in sql i want that in yy/mm/dd format. Please give me directions ASAP? Thanks in advancedimrd_SQL

Todd thanks your blog is awesome but i am so dull that i am not able to figure out as i am using SSIS very first time. While using derive column i am not able to figure out what to choose in Expression, derived column, derived column name, data typ etc. Actually on oracle side date is in mm/dd/yy format and on sql i want in sql default format that is in yy/mm/dd format. Please explain me Thanks dimrd_SQL

TODD I DID EXACTLY WHAT YOU SAID I JUST PUT THE FOLLOWING THING IN EXPRESION (DT_DBTIMESTAMP)("20" + SUBSTRING([LAST_UPDATE_DATE], 7, 2) + "-" + SUBSTRING([LAST_UPDATE_DATE], 1, 2) + "-" + SUBSTRING([LAST_UPDATE_DATE], 4, 2)) ONLY THE NAME OF THE COLUMN WAS CHANGED BUT IT STILL GIVES THE BIG ERROR WHEN I CLICK OK ON DERIVED COLUMN PAGE AND WANT TO PROCEED AND THE ERROR IS VERY BIG ERROR IS: FAILED TO SET PROPERTY EXPRESSION ON "RECENT DATE" COLUMN ACTUALLY I USED THE SAME THING IN EVERY ROW IN EXPRESSION COLUMN. THANKS dimrd_SQL

Hello todd Thanks first of all for the interest Actually what i am doing is i am putting (DT_DBTIMESTAMP)("20" + SUBSTRING([column], 7, 2) + "-" + SUBSTRING([column], 1, 2) + "-" + SUBSTRING([column], 4, 2)) expression with changing the column name . Secondly in derived column i am choosing replace "column" and in derived column name i am giving some name. But again i am getting error. What shd i do now Thanksdimrd_SQL

hi todd i did the same but now again i am getting the error. Now i will explain that all. Error: Error at data flow task[derived Column[1553]]: The function "SUBSTRING" does not support the data type "DT_DBTIMESTAMP" FOR parameter number 1. The type of the parameter could not be implicitly cast into a compatible type for the function. To perform this operation, the operand needs to b e explicitly cast with a cast operator. Error at data Flow Task[Derived Column[1553]]: Evaluating function "SUBSTRING" failed with error code 0*c0047089 Error at data flow task[derived column[1553]]: Computing the expression "(DT_DBTIMESTAMP)("20" + SUBSTRING([column], 7, 2) + "-" + SUBSTRING([column], 1, 2) + "-" + SUBSTRING([column], 4, 2))" FAILED WITH ERROR code 0*c00470c5. The expression may have errors , such as divide by zero, that cannot be detected at parse time, or there may be an out of memory error. Error at data flow task[Derived Column [1553]]: The expression "(DT_DBTIMESTAMP)("20" + SUBSTRING([column], 7, 2) + "-" + SUBSTRING([column], 1, 2) + "-" + SUBSTRING([column], 4, 2))" on"output column "Derived Column 1" (1679)" is not valid. Error at data flow Task [Derived Column[1553]: failed to set property "Expression" on " output column " Derived Column 1" ( 1679)" is not valid. Error at data flow task [Derived Column [1553]]: failed to set property "Expression" on"output column "Derived Column 1" (1679)". Additional information: Exception from HRESULT: 0*c0204006(Microsoft.SqlServer.DTSpipelineWrap) Thanksdimrd_SQL

Todd I did the same using data conversion task but out of 20000 rows only 1338 rows reach at the destination. I noticed that when the package runs source and destination turns red data conversion task turn green. The error i am getting is: Error 1)[OLE DB Destination [22]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "Invalid date format". 2)[OLE DB Destination [22]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (35)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (35)" 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. 3)[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Destination" (22) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (35). 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. 4)[OLE DB Source [1]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020. 5)[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. Thanks dimrd_SQL

Hi Todd, I agree that our friend seems a bit lost, but there is one explanation regarding "Invalid Date Format" errors when converting from Oracle to SQL Server. The lowest date that SQL server can accept is "1-Jan-1753", whilst oracle can hold dates before this. You can get an error when there is rubbish data in your oracle table and you attempt to convert it to SQL Server. See my post for a transformation script on how to get around it: http://jcrawfor74.wordpress.com/2010/12/19/ssis-date-conversion-error-going-from-oracle-to-sql-server/ Cheers John