An error occurred while extracting the result into a variable of type (DBTYPE_I4) error

Question

I have a simple sql statement which outputs a few columns, whch one of them is a column with an int datatype, but I get the following error when I execute the package, I tried to change the datatype in the flat file but it doesn't seem to work: [Execute SQL Task] Error: Executing the query SELECT schema_name(tab.schema_id) AS schema_name, ... 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. Would you please let me know what could be done to fix the issue? Thank you in advance.

Answer 1

Well, it's quite obvious. You are retrieving a string from the database, and you are try to squash it into an int. So either change the data type of the variable or review the query. (That is, I don't know exactly what you want to achieve.)

Answer 2

Hi @sql dev, Could you please share the setting of the Execute SQL Task?

Make sure that the Result Sets is correct, check SSIS Basics: Using the Execute SQL Task to Generate Result Sets.

change the datatype in the flat file but it doesn't seem to work

You may also try to add the Data Conversion to change the datatype.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 3

ssis to use variable in an expression