I am able to run the stored procedure in management studio.
when i create a dataset in BIDS, the metadata came up, but when i execute the stored procedure in a dataset, its giving me the following error.
"Could not find stored procedure. SQL Server error 2812"
Thanks for your help guys.