Hi All, I'm Trying to get the Sp_depends value for the Stored Procedure. Where I'm passing the name of the sp in a variable and I want to execute that sp in my OLEDB source, For that I have selected the Sql command text and in there I have given the command as "EXEC Sp_depends ?", and In parameter I have given the Parameter0 = User::Sp_Name, But I'm getting error saying parameter @objname not defined. Help me out to get the solution. Thanks, Abdul.

use this way ( Recommended) : create a new package variable of type string, name it for example SQLCommand, then in expression property of this variable write: "Exec sp_depends "+@[User::SP_Name] then set EvaluateAsExpression property of SQLCommand variable to true. then in OLE DB Source, set data access mode as "sql command from variable", and set User::SQLCommand variable there.http://www.rad.pasfu.com

Please check this link :http://social.msdn.microsoft.com/Forums/en-US/sqldocumentation/thread/2cebd1fb-096d-4228-bfb6-76863a80fcd3/ Thanks http://dwhanalytics.wordpress.com/

Hi Reza, Thanks a ton for the immediate reply :) I did the same as you said and I'm getting the following error TITLE: Package Validation Error ------------------------------ Package Validation Error ------------------------------ ADDITIONAL INFORMATION: Error at Extracting the Sp to get Elements [OLE DB Source [104]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "Incorrect syntax near '+'.". Error at Extracting the Sp to get Elements [SSIS.Pipeline]: "component "OLE DB Source" (104)" failed validation and returned validation status "VS_ISBROKEN". Error at Extracting the Sp to get Elements [SSIS.Pipeline]: One or more component failed validation. Error at Extracting the Sp to get Elements: There were errors during task validation. (Microsoft.DataTransformationServices.VsIntegration) ------------------------------ BUTTONS: OK Help me out to get through this. Thanks, Abdul.

you should have a default valid value in User::SP_Name variable http://www.rad.pasfu.com

Yes got man got the answer :) Thanks a Ton.