SSIS OLE DB Command misinterpreting function parameter data types
I have been fighting all morning to create a function which helps do a mind wrangling calculation for a client, and I win. All I needed to do was get SSIS to use it to update a table and my work was done.
Except SSIS decides that after i’ve been melting my brain all morning, what I need is some frustration.
The function accepts 4 parameters – a varchar(50), a datetime, a decimal (13,4) and another decimal (9,2). I then used the parameters in an OLE DB command as follows:
UPDATE TARGET_TABLE
SET DATETIME_FIELD = FUNCTION(?,?,?,?)
WHERE KEY = ?
And SSIS complains about not being able to convert a decimal to a datetime. To be precise:
An OLE DB record is available. Source “Microsoft SQL Native Client” Hresult 0x80040E14 Description “Implicit conversion from data type datetime to decimal is not allowed. Use the CONVERT function to run this query.”.
I scratch my head here, check data types, but no luck with anything obvious. So I turn to SQL profiler to try and work out what it’s doing begind the scenes. And the OLEDB command is sending this to the server to validate:
SET FMTONLY ON select DATETIME_FIELD,DATETIME_FIELD,DATETIME_FIELD,DATETIME_FIELD,KEY from TARGET_TABLE where 1=2 SET FMTONLY OFF
So for reasons that confound me, to validate the SQL it is sending through for each parameter of the function it sends the target column of the SET statement. So validation fails, as not every parameter in my function is a datetime.
Unfortunately putting a CONVERT around the offending decimal columns to convert them to decimal (yeah, I know…) only helps temporarily, as when the command builds the SQL to execute the function, it still insists on sending the parameters as datetimes, and thus it stumbles at validation in a slightly different place.
Unfortunately the only workaround I can think of is to move the structure out of the Data Flow and put it in a loop, putting the SQL into a variable that is used by the Execute SQL task. Ugly, but I can’t see how to adjust what SSIS sends to the database engine.
