CODEWITHSUNDEEP
sql-server-2008ssis
David
David

Reputation: 1041

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

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

Upvotes: 4

Views: 27741

Answers (3)

Michael K
Michael K

Reputation: 439

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'

parameter mapping

Upvotes: 13

NikRED
NikRED

Reputation: 1195

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

Upvotes: 2

billinkc
billinkc

Reputation: 61211

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)

Upvotes: 2

Related Questions