Problems In Mapping Parameters Of Type Int64 To The Execute SQL Task

Good day everyone,


I have a question concerning the Execute SQL task.


Problem Statement:
I have a table containing the following sample data.


Catalog_ID, Product_ID, IsBlocked
56789, 1, 0
56789, 3, 1
23567, 1, 0
23567, 2, 1


The data types of the fields are:
Catalog_ID: bigint
Product_ID: bigint
IsBlocked: bit


I have two variables called "old_catalog_id" and "new_catalog_id" with the following values:
old_catalog_id: 56789
new_catalog_id: 11111


Now, I would like to select all the recods, whose Catalog_ID fields equals the value in the variable "old_catalog_id" and insert identical recods with the "new_catalog_id" value.

The result of that operation on my sample records is:

Catalog_ID, Product_ID, IsBlocked
56789, 1, 0
56789, 3, 1
23567, 1, 0
23567, 2, 1
11111, 1, 0
11111, 3, 1

blue: selected fields
green: inserted fields


My Solution:
In order to realize this solution, I have created the following tasks on the Control Flow.

1. Create an Execute SQL Task for the selection. (Name: Selection Task)
2. Create a For-Each-Loop for iterating on the result set. (Name: Loop on results Container)
3. Create an Execute SQL Task inside the For-Each-Loop container for inserting the new records. (Name: Insertion Task)


Configurations and Problems:
1. Selection Task:
General Tab:
Result Set: Full Result Set
SQL Statement:
select Product_ID, IsBlocked
from MyTable
where Catalog_ID = ?


Parameter Mapping Tab:
Variable Name -- Direction -- Data Type -- Parameter Name
User:: old_catalog_version -- Input -- Large_Integer -- 0


Result Set Tab:
Result Name -- Varibale Name
0 -- User::FullResultSet (which has the Data Type: Object)


When I execute this task, I get no records.
Thus, I have hard-coded the value of Catalog_ID in the Sql Statement parameter. Now, I get the correct 2 records in the result set.
Question 1: What am I doing wrong in the Parameter Mapping?


2. Loop on results Container:
Collection Tab:
Enumerator: Foreach ADO Enumerator
ADO object source variable: User::FullResultSet
Enumeration mode: Rows in the first table


Variable Mappings Tab:
Variable -- Index
User:: old_prod_id -- 0 (Data Type of "old_prod_id" is Int64)
User:: old_isBlocked -- 1 (Data Type of "old_isBlocked" is Boolean)


When I execute the package, it fails with the following error message:
Error: 0xC001F009 at SQL Tasks: The type of the value being assigned to variable "User:: prod_id" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Thus, I have changed the data type of the variable "old_prod_id" to Object. Now, the package runs successfully.


Question 2: Why isn't the package accepting "Int64" as the data type of my variable, although the corresponding DB field has the type "bigint"?


3. Insertion Task:
General Tab:
Result Set: None
SQL Statement:
insert into MyTable
(Catalog_ID, Product_ID, IsBlocked)
values (?, ?, ?)


Parameter Mapping Tab:
Variable Name -- Direction -- Data Type -- Parameter Name
User::new_catalog_version -- Input -- Large_Integer -- 0
User:: old_prod_id -- Input -- Large_Integer -- 1
User:: old_isBlocked -- Input -- Variant_Bool -- 2


When I execute this task, it fails with the following error message:
Error: 0x0 at Insertion Task: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Catalog2Context_CATALOGS". The conflict occurred in database "XS_EC_CCH_PEGXSAP2", table "eSearch4.CATALOGS", column 'ID'.


Well, the message implicitly states that the value of the variable "new_catalog_version" has violated the mentioned FOREIGN KEY constraint. But I have set a breakpoint and I saw that the value was set correctly. So, it seems that the Execute SQL Task is not able to read the value of the variable correctly.


Question 3: What am I doing wrong in the Parameter Mapping, which causes the task not being able to read the value of the variables correctly?


Thanks in advance for your help.


Regards,
Samar