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