April 13, 2010 at 11:59 pm
Hi all
I am not sure if I am doing this right and would love to have some help / suggestions with this -
I have a table called progs with two columns (programs and Number) - please find attached the sample data
create table programs (programs varchar(5), num int)
I have a stored procedure called extractdetails which calculates details for each program and presents the information in a table called Finaldetails. At this stage i am running it manually via SSMS where i pass the program as parameter and it works fine...
However I am now trying to run it via SSIS. I have created the following
1. An Execute Task Package which pulls out all the programs and stores it in an Object Variable called - progobjdet
2. Have a for loop which loops through this object variable and stores each program individually in the variable of type string - progid
I would like to execute the Stored procedure so the first time it runs in the loop it executes the child procedure extractdetails and saves the result from finaldetails table into excel and renames the file with the program id appended. I am stuck at the bit of calling procedure. It throws up memory errors or just fails stating the way the procedure is executed is incorrect and not recognised. exact error message -
Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec [dbo].[updatedetails] ?" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Can some one please shed some light on this ....
Thanks
Vani
April 14, 2010 at 7:56 am
Ok, lets work our way through the error..
Do you have the connection set correctly in the General tab?
Does it point to the right database?
Are your variable to input parameters mapping setup correctly in the Parameter Mapping tab?
Is Resultset set to "Full Result Set" in the General tab?
Is the Resultset mapping set to send resultset 0 to your object variable in the Result Set tab?
These ALL need to be right for this to work..
CEWII
April 15, 2010 at 5:54 pm
Hi
Thanks for your reply. Yes So far I have checked that and got it to work till the bit of accepting all programs and providing information for all programs in one spreadsheet.
I need to update it so that it groups the information by program & saves it as seperate files... Not sure how am going to do this but am going to give it a try
Cheers
Vani
April 15, 2010 at 10:54 pm
Hi all
I have it figured out and please find attached is the document if any one is interested for future
Cheers
Vani
April 15, 2010 at 11:25 pm
Thanks for posting that resource.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 8, 2011 at 7:28 am
vani_r14 (4/15/2010)
Hi allI have it figured out and please find attached is the document if any one is interested for future
Cheers
Vani
It's good of you to post that, but your instructions there don't actually show SSIS executing an SP. You've copied the text of the proc into SSIS and executed that instead. The proc I'm working with is over 1000 lines long so that's not feasible and I still can't get it to accept parameters.
March 8, 2011 at 2:24 pm
Hi
Try using an Exec SQL Task - In here - Add the line "Exec SP Name" Set Bypass Query - False
Go to the parameters tab and add the parameters in
This may help
Gud luck
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply