Problems in mapping parameters of type Int64 to the Execute SQL Task

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?

Solution:For starters, have a read here on the BIGINT -> Int64 bug:  (It is verified as fixed in the Nov CTP for SQL Server 2008) https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=260967