August 17, 2011 at 3:47 am
Hi,
Noticed a problem when using a stored procedure as an oldeb data source. Adding a Row Count and Data Viewer, I see a number of rows get extracted and the data flow task runs successfully (turns green).
I audit all my stored procedures to check errors, time they run, complete etc. I noticed that the stored procedure in the data source had run, but didn't complete and an error indicated a divide by zero error. The data flow task had extracted rows up until the divide by zero, which explains why I wasn't getting the number of rows I was expecting. However, the data flow task didn't error and it appeared as if the task completed ok.
So, stored procedure did not run to completion and error'd, however the package extracted a number of rows and 'turned green'. I've since corrected the divide by zero error, but there's no way I want my package to run and not report this error.
My stored procedure accepts a number of input parameters and I've set these up in the oledb data source and map to package variables ok:-
EXEC <procname>
@ExtractMode = ?,
@StartDate = ?
Mappings:
@ExtractMode User::ExtractMode
@StartDate User::StartDate
and I see these parameters are received by the stored procedure ok. This isn't the problem.
What I would like to do is check the return value of the stored procedure, and this is where I am really struggling. I cannot find any documentation on how to do this. I would expect it to be something like:
EXEC ? = <procname>
@ExtractMode = ?,
@StartDate = ?
Mappings:
@RETURN_VALUE User::Result
@ExtractMode User::ExtractMode
@StartDate User::StartDate
The last statement in the stored procedure script ends with RETURN (0 if success or some error code if failed). I cannot get the @RETURN_VALUE from the stored procedure as the oledb data source refuses my parameter/mapping settings and turns red. I've tried a number of combinations trying to get this to work, but no luck. Googling so far hasn't helped.
Does anyone know if it is possible to retrieve the return value from a stored procedure in an oledb data source, if so could you share the syntax, please.......
Thanks
August 18, 2011 at 1:02 pm
I think what you may need to do is to move your stored procedure call to an Execute SQL task. You can get output and return variables from that and put your resultset into an Object variable. You can then evaluate your return and output variables and handle errors. To get your results output, you'll need to use a Script Transformation as your source and move the data from the object variable into rows. Check out http://blogs.msdn.com/b/mattm/archive/2006/11/08/stored-procedures-with-output-parameters.aspx for the Execute sql task and variables and http://consultingblogs.emc.com/jamiethomson/archive/2006/01/04/2540.aspx for getting the results set from a variables into a file. It's not pretty, but it works.
MWise
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply