11.29.2010

SSIS OLEDB Command Transform Invalid Operand Clash Error Workaround

I recently needed to use an SSIS OLE DB Command Transformation to call a stored procedure (sproc) to update some values in a database table for a SSIS package I was working on.

One of the input parameters to the sproc was a datetimeoffset 

CREATE PROCEDURE [dbo].[usp_Update_DateTime]
@Datetime datetimeoffset,
AS
SET NOCOUNT ON;


-- Update code simplified
PRINT @Datetime;


GO


I kept getting the following validation error:



image



The validation error text from the OLEDB Command Transform was:



Error at Data Flow Task [OLE DB Command [19]]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Operand type clash: int is incompatible with datetimeoffset".



Error at Data Flow Task [OLE DB Command [19]]: Unable to retrieve destination column descriptions from the parameters of the SQL command.



as a side note: even though the text box doesn’t look selectable, you can click inside of it and do a  “Select All” (CTRL-A),  “Copy” (CTRL-C) to get the validation error text into your clipboard.



Workaround



My workaround was to add a data conversion task and cast the datetimeoffset to a DB_TIMESTAMP.



image 



I then had to change the sproc parameter to a datetime. I modified the sproc to convert the datetime to datetimeoffset.



CREATE PROCEDURE [dbo].[usp_Update_DateTime]
@Datetime datetime,
@TimeZone char(6) = '+00:00'
AS
SET NOCOUNT ON;

DECLARE @datetimeOffset datetimeoffset;

SET @datetimeOffset = TODATETIMEOFFSET(@datetime, @TimeZone);

-- Update code simplified
PRINT @dateTimeOffset;


GO


This only worked since all of my incoming times were coming in normalized to UTC already. If they weren’t, I would have needed to pass in  value for @timeZone offset parameter.



Conclusion



I suspect that this problem occurs with the other newer date/ time parameters as well , but did not have time to test. While the workaround seemed to work ok, it was certainly a bunch of extra hoops and it also took me about 30 minutes of screwing around with SSIS to figure out that I wasn’t doing anything wrong. (The actual sprocs and dataflow were much more complex, and I had convinced myself SSIS was caching the parameters data types somehow)



 



 



Links



Date and Time Functions (Transact-SQL)



Integration Services Data Types

No comments: