May 5, 2010 at 8:50 am
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
May 5, 2010 at 9:40 am
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
May 5, 2010 at 9:49 am
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.
May 6, 2010 at 6:48 am
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
May 6, 2010 at 7:19 am
Try this EXEC BIW_Status ?, ? and map the parameters correctly
May 6, 2010 at 7:29 am
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
May 6, 2010 at 7:51 am
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 6, 2010 at 8:09 am
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
May 6, 2010 at 8:18 am
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 7, 2010 at 1:07 am
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
May 7, 2010 at 1:37 am
Well done for getting there in the end and thanks for posting your solution - glad to help.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 7, 2010 at 7:41 am
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:
😛
May 7, 2010 at 7:50 am
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
May 7, 2010 at 8:22 am
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 😎
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 7, 2010 at 8:47 am
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 "
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply