Execute SQL Task : Input and Output parameters in tsql stataments with ADO.NET connection type

Hi Everyone,

I haven't been able to successfully use the ADO.NET connection type to use both input and output parameters in an execute sql task containing just tsql statements (no stored procedure calls). I have successfully used input parameters on their own but when i combine it with output parameters it fails on the simplest of tasks.

I would really find it beneficial if you could use the flexibility of an ADO.NET connection type as the parameter marker and parameter name can be referenced anywhere throughout the sql statement in no particular order. The addition of an output parameter would really make it great!!

Thanks

What is the error you get?

have you search this forum and/or the web

http://sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx

http://forums.microsoft.com/MSDN/Search/Search.aspx?words=ADO.NET+parameters&localechoice=9&SiteID=1&searchscope=forumscope&ForumID=80

|||When you say "output parameters" are you talking about using the result set feature?

Note that the result name should be 0, 1, 2, etc... when mapping to a variable name.|||For more than one result, Make sure that you are using the result set of "Full Result Set" then shred this result set using a for each loop, and assign each to a variable.|||

Hi,

By output what i am referring to is the direction of the paramter. What i am trying to do is pass an an input and an output parameter to an execute sql task with the ado.net connection type and to populate a variable in the package, User::Test2, with the value being returned by the output parameter.

ResultSet : None

SQLSourceType: DirectInput

SQLStatement:

Update dbo.table1

Set value = @.Test1

Set @.Test2 = 'Test 3'

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

The error i am getting is

Error: 0xC002F210 at Execute SQL Task 2, Execute SQL Task: Executing the query "

Update dbo.table_1

Set value = @.Test1

Set @.Test2 = 'Test 3'

" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 4 ("@.Test2"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Thanks

|||

PK2000 wrote:

Hi,

By output what i am referring to is the direction of the paramter. What i am trying to do is pass an an input and an output parameter to an execute sql task with the ado.net connection type and to populate a variable in the package, User::Test2, with the value being returned by the output parameter.

ResultSet : None

SQLSourceType: DirectInput

SQLStatement:

Update dbo.table1

Set value = @.Test1

Set @.Test2 = 'Test 3'

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

The error i am getting is

Error: 0xC002F210 at Execute SQL Task 2, Execute SQL Task: Executing the query "

Update dbo.table_1

Set value = @.Test1

Set @.Test2 = 'Test 3'

" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 4 ("@.Test2"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Thanks

Use the resultset feature to get output. I'm trying to figure out why you'd want to assign a literal value to an output parameter in a SQL task. There are easier ways of getting that populated. The way you've got it setup only has inbound parameters.|||

Hi Phil,

I will try the resultset feature.

Why i was assinging a literal value to an output parameter was only to test that it works, that is User::Test2 is assigned the value "Test3".

Thanks

|||

Hi Ryan,

I am not actually returning a resultset back. If possible, what i am trying to do is assign a value to @.Test2 in the sql statement which will map this value back to User::Test2. In the Parameter mapping i have declared the following:

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

Thanks

|||

PK2000 wrote:

Hi Ryan,

I am not actually returning a resultset back. If possible, what i am trying to do is assign a value to @.Test2 in the sql statement which will map this value back to User::Test2. In the Parameter mapping i have declared the following:

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

Thanks

You can't. Use the resultset feature to return a value from the SQL to a variable.|||

Thanks Phil and Ryan!

I was able to use a resultset and then shred the resultset using a foreach loop.