SSIS: Exec SQL Task: Capture return code of stored proc

SSIS: Exec SQL Task: Capture return code of stored proc

• hyl

  Using the sample DB procs and your settings worked in my environment.
  
  I then went back to my simple test, and modified my test procedure from:
  
    create proc testingdups
    as
    return 99
  
  to be
  
     create proc testingdups (@a int)
     as
     return 99
  
  I then plugged in a dummy input parameter, and everything worked.  Much like the AdventureWorks example.
  
  Since it worked, I went back and plugged in the original stored proc that does not utilize a input parameter, and again, it fails.
  
  So, is there something internal that when input/output parameters are utilized, the parameter mappings are succesful, but if no input/output parameters are required for proc execution, the return code is not being returned correctly
  
  Is it possible for someone else to give this an attempt.  The procs are now basically just stubs, returning a code. 
  
  Alot of our procs we have check return codes, and do not require parameters since control tables are utilized to extract values for processing.
  
  Thanks In Advance....
  
  
  

  
  

• Anitha51560

  I see this error now. This looks like a bug to me. Can you file a bug at Betaplace
  
  Thanks
  Ranjeeta
  
  

• LiFo

  Depending on which Connection Manager you use the parameter setting would differ. Have a look at Kirk's reference. This might help you.
  
  http://sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx
  
  Thanks
  Sutha
  
  
  

• JohnStallo

  I tried

  Exec = uspGetEmployeeManagers 1

  on AdventureWorks database, And I used the following

  User::Variable ReturnValue LONG 0

  in the Parameter Mapping.

  It worked for me.

  Returned 0 in this case.

  User::Variable was defined as Int32 here.

  Can you try the above procedure to see if you can get this working.

  
  
  

• roraima

  Have a look at this article as we do very similar things with a proc and 
  the ExecuteSQL task.
  
  The ExecuteSQL Task
  (http://www.sqlis.com/default.aspx 58)
  
  
  allan
  
  "Ranjeeta@discussions.microsoft.com" 
   wrote in message 
  news:06e042e9-1d8f-48eb-9d9e-2f1f0195bf60@discussions.microsoft.com:
  
  > Is the stored procedure you are using expecting input parameters that
  > you are not supplying  I just tried it with a simple procedure, and it
  > works for me.
  
  
  
  
  
  
                                              
  

• Pascalsz

  Basically, I created a proc requiring no input/output parameters.  I am just trying to get the return code.
  
  Did you use the same format for sql   "exec = testingdups"
  
  If so, how did you define the parameter parameter name, etc
  
  I still can not seem to get the blasted thing working....
  
  The SQLIS article is slightly outdated in fact that new parameter name is not developed at that point..
  
  Thanks for any help....
  
  

• Jim Parish

  Is the stored procedure you are using expecting input parameters that you are not supplying I just tried it with a simple procedure, and it works for me.
  

  
  
  

• batchelert

  No problem, the issue was just logged.  Thanks for everyone help in proving I wasnt losing my mind. 
  
  
  

• MarkBruce

  I have tried the OLEDB and ADO.net, and have attempted the variable expressions listed below.
  
  Connection Managers and Substitution Parameters for Queries

  Connection Manager Type< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />	Parameter Marker	Parameter Name	Example Query	Example Parameter Name
  OLEDB		0 1 …	SELECT Name FROM People WHERE Name LIKE	0
  ADO.Net	@Varname	@Varname	SELECT Name FROM people WHERE Name LIKE @Name	@Name
  < xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" />ADO		@Param1 @Param2 …	SELECT Name From People WHERE Name LIKE	@Param1
  ODBC		1 2 …	SELECT Name FROM People WHERE Name Like	1

  
  
  
  When attempting the OLE connection, using the following SQL "exec = testingdups", and the parameters is mapped as "VRC, return value, long, 0", it states:
  
  

  Error: 0xC002F210 at Execute SQL Task 1, Execute SQL Task: Executing the query "exec = testingdups" failed with the following error: "Invalid parameter number". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

  
  When attempting the ADO.net, using the SQL "exec @varname = testingdups", and the parameter is mapped as "vRC, return value, int64, @varname", it states:
  
  

  Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec @varname = testingdups" failed with the following error: "Could not find stored procedure 'exec @varname = testingdups'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

  The stored procedure does exist, and the query parses. 
  
  I must be doing something really stupid or missing something quite obvious....
  
  TIA
  
  

  
  
  
  
   

  
  
• SSIS: Exec SQL Task: Capture return code of stored proc