How To Use OLE DB Command To Update A Derived Column

Hi,

In my SSIS package,

1. I read from a source namely SOURCE. SOURCE is defined like

CREATE TABLE SOURCE

( f1 int, f2 int, f3 int, id int)

, where f1 is unique and id is always null after reading.

2. pass it to a Slow Change Dimension control to separate the new records and changed records

Then for new records,

3. link to a Derived Column control to add in some new columns

4. link to a OLE DB Command control to update field id before write to destination



In Step 4, I use the command :



INSERT INTO global_id_pool

SELECT MAX(id) + 1

FROM global_id_pool



UPDATE SOURCE

SET id =

(SELECT MAX(id) FROM global_id_pool)

WHERE f1 = ?



Using the command, the parameter can't be recognised with an error reported in BIDS. So eventually I had to change the query to the following to make it work



UPDATE SOURCE

SET id =

(SELECT MAX(id) + 1 FROM global_id_pool)



INSERT INTO global_id_pool

SELECT MAX(id) + 1

FROM global_id_pool



I don't mind the parameter can't be added. But after running, I found id field in the table after the OLE DB Command control is still NULL while the field in the SOURCE table in database is updated. So it seems the OLE DB Command worked on database but the data in memory cache wasn't affected.



So I just wonder if there is way to UPDATE the cache in OLE DB Command control. Many thanks for any help.