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

sql dev 6 Reputation points
2023-04-07T12:17:55.7433333+00:00

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.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,466 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Erland Sommarskog 102.3K Reputation points
    2023-04-07T21:11:43.81+00:00

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

    0 comments No comments

  2. ZoeHui-MSFT 33,386 Reputation points
    2023-04-10T02:56:01.5666667+00:00

    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.

    0 comments No comments

  3. Sai Teja Mikkilineni 0 Reputation points
    2024-05-10T19:35:56.48+00:00

    ssis to use variable in an expression

    0 comments No comments