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

Answer 1

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'

Answer 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)

Answer 3

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