SSIS Derived Column Conditional Time Modification I'm using SSIS to transfer data from a MySQL database to a SQL Server one. There's one point that I'm currently stuck on, however. MySQL TIME datatype supports values greater than or equal to 24:00:00, whereas SQL Server TIME caps at 23:59:59. One of the tables I'm transferring has a TIME column that, in MySQL, is 24:00:00. As logically in our business case this is a 'stop time' and thus equivalent to 00:00:00, I'm attempting to find a way to use SSIS to convert to that should that value come up.

Derived columns seem the way to go about doing this, but I don't seem to have a firm grasp on the syntax and very few resources are out there for TIME literals in SSIS conditional expressions. This is what I'm attempting to work with so far:

time == '24:00:00' ? time = '00:00:00' : time = time


Which of course returns only errors as I'm certain that part of that syntax is incorrect. I have (I believe) the logic, just spottily documented syntax is the issue.
So close. It'd be

time == "24:00:00" ? "00:00:00" : time


The Ternary Operator looks like (condition) ? true : false With the notable exception of the ForEach Enumerator, nowhere else in SSIS expressions are you able to use an assignment like time = "00:00:00". It'll basically be implied by whatever column or variables it's being assigned to.

If the data type of time column is actually detected as DT_TIME the above expression is likely to break because that would be outside the allowable domain for DT_TIME values but it'd actually break/fail/error at the source component level (OLE DB/ADO NET/ODBC source)