In my integration, I am using an “After Document Success” SQL Task to write a tracking record to show the success or failure of my integration data.
In this case, the task calls a stored procedure, which takes information about the integration as string data, and writes it to the tracking table.
SQL Task Conversion Error
I received the following conversion error. However, I’m using this same SQL Task in other maps, and it works fine, so we know it isn’t an issue with the actual code. I’ve deleted the task and re-created it a few times and have gotten the same error.
Task SET_TRACKING failed. Integration run will end reporting failure. Conversion failed when converting date and/or time from character string.
Because this SQL Task script works in other maps, you might think this issue stems from this specific task. However, after recreating the task multiple times with the same results, it rules out the prediction that this is just a “broken task” issue, which is rare but not impossible.
Disabling the task made the error go away, so we know it is something within the task. From the error message, we can see that this is a string-to-datetime conversion issue.
The SQL Task is calling a stored procedure, passing in the three values as string variables. The stored procedure then uses a convert() function to convert the @DATE, which would be a string value, to a proper datetime value to insert into the tracking table. The @mapid and @PK values are already varchar datatypes and do not require conversion.
From the error, it is clear this datetime conversion is what is failing. However using the _MYDATE field in the GP shows fine in the date preview, and the destination shows the correct datetime being used.
To determine the issue, we need to see what is being used in the actual SQL statement, and the only way to do that is to run a SQL Profiler Trace.
Because the source data previewed correctly in SmartConnect and went to the destination as the correct true datetime, the only explanation for this error is that the variable was not replaced properly by SmartConnect, and the literal string was passed to SQL, making the conversion fail.
With the SQL Profiler Trace, we proved that this was happening because we can see that _MYDATE was sent to SQL, unlike the other two values that were properly replaced with the respective data as expected.
Solution
So the question is, “Why was the token NOT replaced?”
The reason is that the replacement of field and variable names in the SQL Task are case sensitive.
If we use the “Insert Variable” lookup, we can see that the field _MYDATE is actually _mydate in the source data, as we can see in the Variable Lookup window below.
In the SmartConnect field mapping window, the UI forces the case to uppercase so the mapping would be IS _MYDATE. But the name of the field in the SQL Task doesn’t match, so the replace doesn’t happen, and the error is thrown by SQL.
It is always recommended to use the lookup window to make sure that we get the proper names. The solution to fix this issue is to use _mydate and not _MYDATE in this example. After fixing this, the value was properly replaced, and the error was eliminated.
If you have any questions, please email support at support@eonesolutions.com.
