How can i use Parameter Mapping in Execute SQL Task in SSIS?

Question

I am trying to retrieve the value of Key from a table with a simple select statement in SSIS through Execute SQL Task. But have no luck figuring out this error.

I have used one input variable with string data type and used this variable in parameter mapping in Execute SQL Task.

Executing the query "SELECT cast([Key] as Int) FROM Table where column = ?" failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_I4)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Note : Datatype for Key column is tinyint

Michael K · Accepted Answer · 2015-08-11 13:29:30Z

13

This message occurs when the default datatype for the parameters remains as 'LONG' instead of whatever is necessary... In your case, this should be 'BYTE'

answered Aug 11, 2015 at 13:29

Michael K

4393 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

billinkc · Accepted Answer · 2012-06-19 02:50:16Z

2

Tinyint is not i4, it's DT_UI1. http://msdn.microsoft.com/en-us/library/ms345165.aspx

If you change your SSIS type to byte then you should be able to assign the results of your query to the value.

Variable User::input  Data Type Byte  Value 2
Variable User::output Data Type Byte  Value 0

Source query SELECT CAST(1 AS tinyint) AS [key], ? AS foo

Execute SQL Task, OLE DB CM, single row result set Parameter mapping tab

Variable Name: User::input
Data Type: Byte
Parameter name: 0

Result of column 1 mapped to User::output

Inspect value after Execute SQL Task and result is 2 (expected)

edited Jun 19, 2012 at 2:50

answered Jun 19, 2012 at 2:42

billinkc

61.4k10 gold badges110 silver badges169 bronze badges

Comments

NikRED · Accepted Answer · 2012-06-19 16:15:55Z

2

Write the query like -> SELECT cast([Key] as Int) as Key FROM Table where column = ?

answered Jun 19, 2012 at 16:15

NikRED

1,1953 gold badges22 silver badges40 bronze badges

Collectives™ on Stack Overflow

How can i use Parameter Mapping in Execute SQL Task in SSIS?

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related