Failed to call stored procedure with output datetime parameters in SQL task
Hi, I have a stored procedure like: create PROCEDURE dbo.testProc -- Add the parameters for the stored procedure here @para1 datetime output AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; set @para1 =GETDATE() END GO and in SSIS, i created a SQL task with OLE DB connection calling this SP, the statement in SQL task is: exec testProc @para1=? OUTPUT, I want to retrieve the parameter @para1 to my SSIS variable "BusinessDate", the mapping is: User::businessDate , OUTPUT , Date ,0 , -1 However, SSIS always gives the following error: Error: 0xC002F210 at Get Business Date, Execute SQL Task: Executing the query "exec testProc @para1=? OUTPUT" failed with the following error: "Error HRESULT E_FAIL has been returned from a call to a COM component.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Get Business Date Can anybody help me out? Thank you Liang(China) From SAP
April 19th, 2011 10:34pm

Hi Liang Firstly, your datatype for the parameter needs to be DBTIMESTAMP, not DATE. Secondly, ensure that your "ResultSet" property on the Execute SQL task is set to "None" Maybe this will help http://blogs.msdn.com/b/mattm/archive/2006/11/08/stored-procedures-with-output-parameters.aspxCraig Bryden - Please mark correct answers
Free Windows Admin Tool Kit Click here and download it now
April 19th, 2011 11:09pm

Hi Craig, Thank you for your suggestion, yet however it doesn't work. when changing the datatype to DBTIMESTAMP, it will give an error message like below: Execute SQL Task, Execute SQL Task: Executing the query "EXEC [dbo].[testProc] ? OUTPUT" failed with the following error: "The type of the value being assigned to variable "User::businessDate" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Any idea? Liang(China) From SAP
April 20th, 2011 12:20am

Hi Liang I am struggling to understand where this is going wrong without your code. What datatype is your variable "User::businessDate" set to in the package? Can you post a screenshot of the different tabs of your Execute SQL task Have a look at this post to understand how data types map across SSIS and SQL Server (Go right to the bottom) http://msdn.microsoft.com/en-us/library/ms141036.aspx HTHCraig Bryden - Please mark correct answers
Free Windows Admin Tool Kit Click here and download it now
April 20th, 2011 12:48am

Here is what needs to be put in execute sql task. Result set - Single row , variable BusinessDate - datatype datetime. This worked for me and was able to ftech the correct date. Place break point on post execute of execute sql task and you can get to see the value of variable in the locals window. DECLARE @Date DATETIME EXEC testProc @Date OUTPUT SELECT @DatePlease mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Deepak.
April 20th, 2011 2:11am

I did a successful test of the stored procedure in SQL Server 2008 BIDS. User::BusinessDate setup as datetime variable Execute SQL Task Valid/tested database connection setup Stored procedure called as posted in the first post : exec testProc @para1=? OUTPUT Parameter mapping: User::BusinessData output dbtimestamp 0 -1 Script task for MsgBox display: Microsoft Visual C# 2008 public void Main() { string FileName; FileName = (Dts.Variables["User::BusinessDate"].Value).ToString(); MessageBox.Show(FileName); Dts.TaskResult = (int)ScriptResults.Success; } Kalman Toth, SQL Server & BI Training; SQL 2008 GRAND SLAM
Free Windows Admin Tool Kit Click here and download it now
April 20th, 2011 2:32am

You didn't say anything about yout SSIS Version. If 2005, change the datatype of the variable to string and return the date as varchar from your proc. SSIS2005 has a few problems with date-variables for input and output
April 20th, 2011 8:04am

Hi Craig, Today, i tried again, now it works, so wierd.... i changed nothing anyway, your point is correct: the parameter should be set as type DBTIMESTAMP. Thank you, everybody, really appreciate all your helpLiang(China) From SAP
Free Windows Admin Tool Kit Click here and download it now
April 21st, 2011 7:51am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics