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.