HOW TO MAP A VARIABLE TO A PROC IN EXECUTE SQL TASK, USING OLEDB CONNECTION

Question

HOW TO MAP A VARIABLE TO A PROC IN EXECUTE SQL TASK, USING OLEDB CONNECTION

SSIS\SSRS\SSAS

SSCertifiable

Points: 6038
More actions
September 2, 2008 at 12:44 pm

#212050

I have a two execute sql task. the first one has a small select statements, and returns a value mapped to a variable called "ValueToPass". Then I used this varibale in the 2nd execute sql task as input parameter for my proc that i am calling.
I am using OLEDB connections.
The first task works fines; it returns the value based on my select .
the second one I am worried for is that mapping is not done in rt way
(thats what I belive). When I use ADO.NET COnnections it works fine ( and I preefer ADO.net conns when i am exec proc in SSIS, But i just don't want to create one more connections for just one package and have another config file for that connections)
HOW DO U MAP A VARIABLE IN EXECUTE SQL TASK?
In the parameter mapping page , I have variable "ValueToPass" as Input direction, datatype-varchar, parameter name 0 and size -1
In the general page i have
EXEC dbo.myprocedure 0 .
Am i doing wrong here or it shoudl be like EXEC dbo.myprocedure ?
I am not sure here what goes after my proc name........
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

I know you don't want the 0 as your place holder. I'd go with the "?"

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

SSIS\SSRS\SSAS SSCertifiable Points: 6038 More actions · Answer 2

Jack I know. But I tried putting ? , it gives me parse error, it says all kinds of errors lik e---parameters not map corrrectly.

But When i put 0 it works , meaning that I can parse , but the logic within the proc is not working. SO i don't know.

Rt now I have ADO.NET connections which works perfectly fine. But if possible i want to avoid ado.net conns and use oledb conns

Thanks

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

I have always used oledb connections and the "?" is the parameter placeholder in oledb. For the ADO.NET connections to SQL Server maybe you need to provide the parameter name like "@parameter"?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

VG-619426 Hall of Fame Points: 3914 More actions · Answer 4

Hi,

It should be like this : EXEC dbo.myprocedure ?

I had a same situation, I didn't wanted to create one more ADO.NET conections. So, I used like this and it works...

VG

SSIS\SSRS\SSAS SSCertifiable Points: 6038 More actions · Answer 5

Yeha Jack u r RIght for ADONET @parameter works fine, and when you write your sql statement it becomes like exec dbo.myprocedure @parameter , .... and so on

amy be U could help me out here. On the first execute sql task i have a variable "ValueToPass" mapped to return a resultset as a single row.

Then On the next execute sql task I map this variable to a paramter 0 as input with varchar datatype

Then what i do in the sql statement.

let me guess

exec myprocedure ?

but its failing even the prase .