It appears the SSIS doesn't support the date datatype as an input to a stored procedure. Please advise...Say you have the following procedure defined. CREATE PROCEDURE usp_SSIS_Date_Test @SubmitDate date AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here END GO Then you want to use it in an OLE DB Command in a data flow.The following error results: "Operand type clash: int is incompatible with date",Error at Data Flow Task []: Unable to retrieve destination column descriptions from the parameter of the SQL command.If I change the type from "date" to "datetime" in the stored procedure, everything works great. I would like to use the procs (hardened in production) as is. Short of wrapping all of them in procs with "datetime" and do the conversion myself, any suggestions?

How are you "using" the stored procedure? In an Execute SQL Task? An OLE DB Command?

Hi, what is the your variable data type in SSIS? Please check the datatype your are using for storing SP result. Thanks-Let us TRY this | Dont forget to mark the post(s) that answered your question

Variable type is not a matter here i think. Because it gives error before you do your mapping, not after you you do it, and unfortunately Mr. Originate is right, ssis somehow doesnt realise the data type Date.BI and ERP Senior Consultant @ Nexum Bogazici Personal Blog: http://www.ssisnedir.com/blog - If it is, Please dont forget to mark as answered.

What version of SSIS are you using?

ssis 2008. used in Oledb command.BI and ERP Senior Consultant @ Nexum Bogazici Personal Blog: http://www.ssisnedir.com/blog - If it is, Please dont forget to mark as answered.

I experience the same problem. I could not find a Connect bug issue for this. I also experience that BIDS will actually abort if the stored procedure uses a user defined data type from a money or smallmoney type as in: CREATE TYPE t FROM smallmoney NULL<br/> CREATE PROCEDURE p @p t AS RETURN 0 Greg

Definitely file a Connect issue for that one (link back here). Does "EXEC usp_SSIS_Date_Test CAST(? AS DATE)" work? (I'm not a T-SQL expert.) Talk to me now on

It appears to be an issue with the definition of the stored procedure, not the syntax of the EXEC statement. I don't think casting an argument in the EXEC statement makes a difference. One can workaround the issue by changing the parameter definition of the stored procedure to use the built-in datatype instead of the user defined one. This should be logged into Micrsoft Connect if it not logged already.Greg

I have experienced the same issue... Any task that uses an OLEDB connection and passes a parameter that is defined as "Date" will fail with the below error. Changing the parameter to "DATETIME" resolves the issue. To recreate the issue Create the following stored procedure: CREATE PROCEDURE foodate ( @inDate DATE ) AS SELECT @inDate; In SSIS create an Execute SQL task with an OLEDB connection and try to execute the package. It should fail with the error listed below. Now change the Procedure to use a DATETIME CREATE PROCEDURE foodate ( @inDate DATETIME ) AS SELECT @inDate; The SSIS Package will now execute successfully. This seems to be an input issue only, as I have sevral packages that return data of type DATE without issue. Also I have not tried this on anything other than SQL Server but from reading the issues stated here and on other websits it seems to be an issue with ALL RDBMS sources. My OLE_DB connection is to a SQL 2008 Server (ver 10.0.2714) ---- ERROR ----- Error 13 Validation error. Load base data to Client Hier tables: Load base data to Client Hier tables: 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 date". CU.Client_Hier_Sum_Load.dtsx 0 0 Error 14 Validation error. Load base data to Client Hier tables: Load base data to Client Hier tables: Unable to retrieve destination column descriptions from the parameters of the SQL command. CU.Client_Hier_Sum_Load.dtsx 0 0 Error 14 Validation error. Load base data to Client Hier tables: Load base data to Client Hier tables: Unable to retrieve destination column descriptions from the parameters of the SQL command. CU.Client_Hier_Sum_Load.dtsx 0 0

We have just reproduced the behaviour mentioned by Chad in one of our current projects. Does someone have a connect id at his fingertips? Thanks in advance and best regards, Gerald

In the meantime I have found the corresponding connect entry (ID = 628743): http://connect.microsoft.com/SQLServer/feedback/details/628743/ssis-oledb-command-date-datatype-in-stored-procedure-sqlcommand-yields-operand-error

Has anyone found a work around to this, apart from touching the stored procedure...I dont have rights to do that. All I can do is modify the package. I checked the connect item, and it is closed saying that the issue is not reproducible :(

If you can't modify the stored proc, then wouldn't something like this work? DECLARE @tempvar AS DATETIME SET @tempvar = ? DECLARE @datevar AS DATE SET @datevar = CAST(@tempvar AS DATE) EXEC your_sp @datevar Talk to me now on