I have just taken the leap from dts packages to ssis packages I have a package with ADO.NET connections and I have a Excute SQL Package which runs a stored procedure through the ADO.NET connection which accepts four parameters and outputs one. I have got the SQL statement looking like this (based on MSDN) EXEC dbo.sp_AddDocumentWorksite @DocNum,@CaseCode,@Description,@CreateJob OUTPUT @DhUniq but it fails saying the parameters are wrong. If I remove the OUTPUT @DhUniq and the associated parameter out of the parameter list it runs, but I need the output for the next step I suspect I have got something wrong with the syntax Thanks

I suspect you did not map the output parameter. Follow this how to: http://www.julian-kuiters.id.au/article.php/ssis-execute-sql-task-output-parametersArthur My Blog

Still no joy, my parameter screen looks like suggested article :(

Could you post the error you are getting?Arthur My Blog

[Execute SQL Task] Error: Executing the query "dbo.sp_AddDocumentWorksite " failed with the following error: "Could not find stored procedure 'dbo.sp_AddDocumentWorksite '.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

You either not connecting to the correct SQL Server instance or more likely have a typo in the name of the stored procedure or query.Arthur My Blog

You either not connecting to the correct SQL Server instance or more likely have a typo in the name of the stored procedure or query.Arthur My Blog

Take out the output parameter OUTPUT @DhUniq from the query in the SQL statement in the execute sql task and then go to parameters tab add new parameter which direction OUTPUT and map it. If you excepting the result set enable "FULL Result set option" in general tab. By default every stored procedure in the Sql server returns the value output with 1 for success and 0 for failure may be that conflicting your result.latha

Take out the output parameter OUTPUT @DhUniq from the query in the SQL statement in the execute sql task and then go to parameters tab add new parameter which direction OUTPUT and map it. If you excepting the result set enable "FULL Result set option" in general tab. By default every stored procedure in the Sql server returns the value output with 1 for success and 0 for failure may be that conflicting your result.latha

The stored procedure has been supplied by an external supplier, and it's not anything I should change. If I remove the OUTPUT @DhUniq from the query, then run it through debugging it excutes fine but I don't get output which I need for the next step. If I add a new parameter with diection OUTPUT into the parameter tab, and debug it - stills fails

DECLARE @OUTPUT INT EXEC TESTPROC @OUTPUT OUTPUT SELECT @OUTPUT Something in simialr lines. Use Single result set and it worked for me . Happy to help! Thanks. Regards and good Wishes, Deepak.

I beleive because your stored procedure is missing the OUTPUT declaration you cannot capture the output. Examine if your SP contains code like: CREATE PROC sp_TheirProc @TheOutvalue INT OUTPUTAS SELECT @TheOutvalue = 10 If this OUTPUT keyword is missing then you cannot capture the output. In other words I now believe the issue is with their stored procedure. Arthur My Blog

I have searched the supplier's sp and their is no OUTPUT. And just to prove I wasn't done anything stupid in my exec SQL task. I quickly set up a test sp and package and it worked fine Thank you for your help