SSIS Execute SQL Task Error on Variable

Question

SSIS Execute SQL Task Error on Variable

Kristie A 0

I'm selecting a Number(38,0) data type field from an Oracle table and am attempting to pass the value returned from that Oracle select statement into an Int64 type variable in Visual Studio.

The query I'm using is

select max(<field_name>) from <table_name>

In the result set of the component, the Result Name is 0 and the Variable Name is my Int64 variable I have set up.

When I attempt to execute the component, I get the following error:

[Execute SQL Task] Error: An error occurred while assigning a value to variable "<variable name>": "Input string was not in a correct format.".

I have an identical process, same data types for the source table and variable to runs fine, but this new "cloned" one is throwing this error. Looking for suggestions.

Yitzhak Khabinsky 26,491 Reputation points

2025-04-30T20:10:34.5066667+00:00

You can try to change SSIS varibale data type from Int64 to a String...

1 answer

Your answer

Yitzhak Khabinsky 26,491 Reputation points

2025-04-30T20:10:34.5066667+00:00

You can try to change SSIS varibale data type from Int64 to a String...

Answer 1

NUMBER(38) means a whole number that can have up to 38 digits of precision. In other words 38 digits. Int64 is a 64-bit whole number. The maximum value that a 64-bit value can hold is 9,223,372,036,854,775,807 which has only 19 digits of precision. You cannot store a NUMBER(38) into an Int64. The equivalent of an Int64 in Oracle is BIGINT.

While it might work on some tables, ultimately MAX(<field>) is returning a value outside the range of Int64 and hence the conversion is failing. You can run your query using SQL Navigator to confirm that the table is getting a value larger than can be stored in Int64 to confirm this.

Once you've confirmed the issue you'll need to decide what data type will work for the data stored in the table.

Share via

SSIS Execute SQL Task Error on Variable

1 answer

Your answer