getting error when passing variables in execute sql task using ADO.NET

Question

Post reply

getting error when passing variables in execute sql task using ADO.NET

Kari Suresh

Hall of Fame

Points: 3712
More actions
May 5, 2010 at 8:50 am

#140698

Hi,
My stored procedure has two parameters: EXEC BIW_Status @p_server, 'U'
One I need to pass as parameter and another one hard coding. Parameter @p_server value will change often.
I created ADO.NET connection in connection managers.
To achieve this I am using Execute SQL Task and created a variable in variables window, say User::Source, data type as String and set value.
In Execute SQL Task editor -> General window: I gave ConnectionType as ADO.NET, SQLSourceType as Direct input, SQLStatement as EXEC BIW_Status @p_server, 'U' and Is QueryStoredProcedure as TRUE.
In Execute SQL Task editor -> Parameter Mapping window: I gave Variable Name as User::source, Direction as Input, Data Type as String, Parameter Name as @p_server and Parameter Size as 100.
And parameter name @p_server is same in my stored procedure also.
If execute the task I am getting error: [p][Execute SQL Task] Error: Executing the query "EXEC BIW_Status @p_server, 'U'" failed with the following error: "The OdbcParameterCollection only accepts non-null OdbcParameter type objects, not SqlParameter objects.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correc[/p]
Is this the way to pass parameter to Execute SQL Task.?
How can I fix this Issue.?
KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

Viewing 15 posts - 1 through 15 (of 20 total)

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

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 1

Is this the way to pass parameter to Execute SQL Task.?

Suresh,

Look at the links below on how to map the Parameters using Execute SQL tasks. Also, try using an Expression i.e, a variable as your Input Type, instead of Direct Input for Execute SQL Task which stores the Expression.. that might just help.. If it doesn't help post some screen shots to help you better

http://technet.microsoft.com/en-us/library/ms140355.aspx

http://www.sqlis.com/post/The-Execute-SQL-Task.aspx

http://www.simple-talk.com/sql/ssis/passing-variables-to-and-from-an-ssis-task

http://technet.microsoft.com/en-us/library/ms141003.aspx

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 2

It looks like you're doing this properly, except a couple things have caught my eye and may be causing your issue (I wouldn't know without testing myself).

1. The error message references ODBC but you state that you have set the SQL Task to ADO.NET?

2. BOL states that the QueryIsStoredProcedure property is read-only and False for all connection types but ADO. It should not be True for ADO.NET

3. It appears that your stored proc has 2 parameters but you are only defining one parameter and hard coding the other. I'm not sure if this can be done this way.

Kari Suresh Hall of Fame Points: 3712 More actions · Answer 3

Please find attached screen shots.

Still I am getting error:

[p][Execute SQL Task] Error: Executing the query "BIW_Status" failed with the following error: "The OdbcParameterCollection only accepts non-null OdbcParameter type objects, not SqlParameter objects.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.[/p]

KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 4

Try this EXEC BIW_Status ?, ? and map the parameters correctly

Kari Suresh Hall of Fame Points: 3712 More actions · Answer 5

Try this EXEC BIW_Status ?, ? and map the parameters correctly

Tried with EXEC BIW_Status, but got same error.

What is the issue with parameters..? I really don't know. Can you tell me what is the problem and how can I resolve this.?

KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

Phil Parkin SSC Guru Points: 246979 More actions · Answer 6

It seems to me that you don't really know what is going wrong here - just 'something'.

If you hard code the parameter values, does the query work OK? Are you certain that your package variables are being set properly? You could add them as derived columns and add a data viewer to check.

What is the value of your ResultSet property?

You could also try using an Expression to set the SQL to be executed, rather than mapping parameters.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Kari Suresh Hall of Fame Points: 3712 More actions · Answer 7

If you hard code the parameter values, does the query work OK?

If I hard code the two parameter values then the task is working fine.

Are you certain that your package variables are being set properly?

How can I know that these variables set properly?

You could add them as derived columns and add a data viewer to check.

I am new to SSIS please tell me how can we do that.

What is the value of your ResultSet property?

No results are getting back. It just set the status in SP.

You could also try using an Expression to set the SQL to be executed, rather than mapping parameters.

please guide me with an example.

KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

Phil Parkin SSC Guru Points: 246979 More actions · Answer 8

I'm sorry but I don't have the time to write all of that stuff out right now.

But - regarding the Resultset property - this is a property of your Execute SQL task which needs to be set to reflect what the task is returning. As your hard-coded version works OK, there is no need to do this - it must be something to do with the variables.

I suggest that you read up on Expressions, they are most useful. You can create an expression to replace most properties in a task, including the SQL to be executed. Your expression will build the query to be executed.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Kari Suresh Hall of Fame Points: 3712 More actions · Answer 9

yep, got it..!

Finally I removed mapping in parameter mapping tab of my execute sql task package and used expressions to get it.

Created total three variables.

One for stored procedure, second for source DB and third for status.

In expressions Tab: I used SqlStatementSource property and expression is:

@[User::sp] + @[User::source] +", "+ @[User::status]

now working fine. Thanks for all your responses Buddies.:laugh:

KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

Phil Parkin SSC Guru Points: 246979 More actions · Answer 10

Well done for getting there in the end and thanks for posting your solution - glad to help.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 11

Kari Suresh (5/7/2010)
yep, got it..!
Finally I removed mapping in parameter mapping tab of my execute sql task package and used expressions to get it.
Created total three variables.
One for stored procedure, second for source DB and third for status.
In expressions Tab: I used SqlStatementSource property and expression is:
@[User::sp] + @[User::source] +", "+ @[User::status]
now working fine. Thanks for all your responses Buddies.:laugh:

I suggested this in the very first comment and i thought u tried it.. B/w i am not trying take Phil's credit here :hehe:

😛

Kari Suresh Hall of Fame Points: 3712 More actions · Answer 12

You both suggested me very good. Thanks for your help. Actually I am new to SSIS. I don't know much, learning one by one. Kindly don't misunderstand.

KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

Phil Parkin SSC Guru Points: 246979 More actions · Answer 13

divyanth (5/7/2010)
I suggested this in the very first comment and i thought u tried it.. B/w i am not trying take Phil's credit here :hehe:
😛

Oooh, so you did - I didn't even see that. Credit & respect dude 😎

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 14

Phil Parkin (5/7/2010)
divyanth (5/7/2010)
I suggested this in the very first comment and i thought u tried it.. B/w i am not trying take Phil's credit here :hehe:
😛
Oooh, so you did - I didn't even see that. Credit & respect dude 😎

@Phil -I did in my very first reply (below)... though not as descriptive as you where. I din't mean to be rude here. If i did, I apologize.

@suresh - I am not misunderstanding you... just said that as wouldn't have wasted your time if you tried that in the very first place..No problem as long as it helps you

"Suresh,

Look at the links below on how to map the Parameters using Execute SQL tasks. Also, try using an Expression i.e, a variable as your Input Type, instead of Direct Input for Execute SQL Task which stores the Expression.. that might just help.. If it doesn't help post some screen shots to help you better "