1

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,is 24:00 actually 00:00 the next day? – Max Vernon Oct 16 '15 at 21:52
  • The date is irrelevant here actually. We're only using TIME, no ticks, no date storage. Basically I need to get SSIS to recognize a 24:00 time value and convert that to 00:00 in a conditional. But yes for logical business needs the two are equivalent. – Dylan Bacon Oct 16 '15 at 21:57
4

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)

  • Such strange syntax. At any rate, so there's no way to convert a DT_TIME (As that is what this is being interpreted as) of 24:00:00 into something else at the SSIS level because, by my understanding, SSIS doesn't even recognize 24:00:00 as valid in the first place? I suppose I could change the data at the MySQL level before transferring if need be. – Dylan Bacon Oct 16 '15 at 22:12
  • That or you bring it in as a string (DT_STR, 8, 1252) and then I'd create a new column, SQLCompliantTime in this Derived Column to handle the "fixed" values. That or a case statement on extract. I prefer to fix as many data problems in the source as possible to make my SSIS as dumb as possible – billinkc Oct 16 '15 at 22:15
  • CASE on extract ended up being the least egregious way to handle this. – Dylan Bacon Oct 16 '15 at 22:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.