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.

2 thoughts on “SSIS OLE DB Command misinterpreting function parameter data types

  1. Hi,
    So finally what solution did you come up with. I am trying to play around OLE DB Command but it seems a bit tricky. and again not much information of it is present on your blog.

  2. I never solved it. This was in the days before I used connect so I haven’t raised a bug for it either. If it still affects SQL2012 I suggest you do so.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>