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

Check out Converting Strings to Dates in the Derived Column Component. Talk to me now on

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

You need to "add a new column" in the Derived Column component - name it what you wish. In the expression, you'll need to use a combination of SUBSTRINGs and string concatenation to get things rearranged, then you can cast this to a date type. You don't want "yy/mm/dd" format. You want ISO/SQL standard. This might work: (DT_DBTIMESTAMP)("20" + SUBSTRING([column], 7, 2) + "-" + SUBSTRING([column], 1, 2) + "-" + SUBSTRING([column], 4, 2)) Talk to me now on

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

That error isn't very useful - I'm sure there were more. I'll have to guess as to why it's not working. I'm guessing that you aren't deriving a "new" column with this information in it - you're trying to replace the value in an existing column. You can't do that, because you can't change a column's data type when you're in a Derived Column transformation. You can do one of two things. In the "Derived Column" column inside the Derived Column, pull the choices down and select "<add as new column>" - OR - remove the "(DT_DBTIMESTAMP)(" from the front of the expression, and the last ")" from the end, then attach a Data Conversion component to the output of the Derived Column to convert your columns to date types. Talk to me now on

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

The problem is that you are choosing "replace column". You can't change the data type of a column. You need to select "<add as new column>" Talk to me now on

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

Ah ha. Well - this whole thread is pretty useless - you already have your data in a datetime data type. When using the expressions we've discussed, we were assuming that your data was in a string format, and were using the string functions SUBSTRING to pull it apart to reformat it in order to convert it into a date data type. But the error you're receiving specifically states that you're attempting to use SUBSTRING on a date-typed column. Therefore, you don't need to convert anything, you already have a date type. Just insert into SQL Server. Talk to me now on

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

OK - there's something really confused in your data flow. You must be mapping things incorrectly, because you will never get an "invalid date format" error when inserting an SSIS date typed column into a SQL Server date typed column. There's no "format" to be invalid. Please check the data types of your columns. Based on the information you've provided, the column you were trying to convert was already a date. You shouldn't need to convert it at all - not with a Derived Column, nor with a Data Conversion component. Talk to me now on