October 26, 2010 at 3:19 pm
Hi All,
My stored procedure results in a table. and now i need to use this result to load the dats to csv. But I am not able to execute the stored procdure in the dataflow task. Since it doesnot give the table information. Please tell me any alternatives or give me ideas to resolve it.
Thanks,
Preensheen
October 26, 2010 at 3:20 pm
Data Flow source adapters do allow for SP execution. Does attempting this give you an error?
October 27, 2010 at 8:14 am
John Rowan (10/26/2010)
Data Flow source adapters do allow for SP execution. Does attempting this give you an error?
Yes, i am trying to pass parameter and and everything correctly but still it is not allowing me to to do so.
Fyi..
I do have some temp tables created in the stored procedure.
October 28, 2010 at 3:31 pm
preensheen (10/26/2010)
Hi All,My stored procedure results in a table. and now i need to use this result to load the dats to csv. But I am not able to execute the stored procdure in the dataflow task. Since it doesnot give the table information. Please tell me any alternatives or give me ideas to resolve it.
Could anyone please help me on this....
Thanks,
Preensheen
October 28, 2010 at 3:38 pm
preensheen (10/27/2010)
John Rowan (10/26/2010)
Data Flow source adapters do allow for SP execution. Does attempting this give you an error?Yes, i am trying to pass parameter and and everything correctly but still it is not allowing me to to do so.
Fyi..
I do have some temp tables created in the stored procedure.
To confirm, in your OLE Source Object, you have it set to called query and something like:
EXEC sp_StoredProc @parameter1 = ?, @parameter2 = ?
If so, you need to go to the parameters object and make sure they're named parameter0, parameter1, etc.
Can you post the errors from the output?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 28, 2010 at 3:54 pm
preensheen (10/27/2010)
John Rowan (10/26/2010)
Data Flow source adapters do allow for SP execution. Does attempting this give you an error?Yes, i am trying to pass parameter and and everything correctly but still it is not allowing me to to do so.
Fyi..
I do have some temp tables created in the stored procedure.
What is the error? It's probably your temp tables. Sometimes SSIS has a hard time gleaning the metadata for the result set when temp tables are used. There's a workaround, but first....what's your error?
October 29, 2010 at 12:22 am
Please see the attached
October 29, 2010 at 11:53 am
John Rowan (10/28/2010)
preensheen (10/27/2010)
John Rowan (10/26/2010)
Data Flow source adapters do allow for SP execution. Does attempting this give you an error?Yes, i am trying to pass parameter and and everything correctly but still it is not allowing me to to do so.
Fyi..
I do have some temp tables created in the stored procedure.
What is the error? It's probably your temp tables. Sometimes SSIS has a hard time gleaning the metadata for the result set when temp tables are used. There's a workaround, but first....what's your error?
I had all the problems becasue i had too many temp tables in the SP. And you are right..It is because it had problem with Cleaning the metadata for the result set .
But now I was able resolve my issue by Select all the columns from the tables and fail the condition at the begining on the SP and it worked.
If there is any other better work around for this..please do let me know.
Thanks,
Preensheen
November 1, 2010 at 8:19 am
That's exactly what I'd recommend to do (obviously from the other post).
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply