In my SSIS pacakge I have an Execute SQL Task with 2 statements:
statement 1:
select coalesce ( max (id), 0)+1 as ID from AAA
statement 2:
Insert into BBB (id) values (?)
In the first statement, I saved the result to an variable ID, and in the 2nd statement, I use this variable ID to insert into BBB id column. Let's say the result of the first statement should be 4, however, after I query table BBB, i found what inserted into BBB is 0.
Did I miss anything here?
Running the first statement generates the result of 1 for an empty table.
The default value for an integer in SSIS is going to be 0.
It would lead me to believe that you are not assigning the result from an Execute SQL Task properly.
Either drop in an Script Task to print the value via information events
public void Main()
{
bool fireAgain = false;
string message = "{0}::{1} : {2}";
foreach (var item in Dts.Variables)
{
Dts.Events.FireInformation(0, "SCR Echo Back", string.Format(message, item.Namespace, item.Name, item.Value), string.Empty, 0, ref fireAgain);
}
Dts.TaskResult = (int)ScriptResults.Success;
}
Or set a breakpoint on the second Execute SQL Task to identify the current value of the variable.
If the value is set correctly, then the only remaining point of failure is the second Execute SQL Task where it would imply you do not have your variable mapped.
As your code sample uses a question mark ? as the place holder, it becomes a question of whether the connection manager is OLEDB in which we have a zero based ordinal system or ODBC in which we have a one based ordinal.
If the problem exists on the first step, then be certain that you have used the correct 0/1 value as the "name" of the result column
