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.

When you update the database after you've already selected from it, you can't expect that updated data to show up in the data flow stream. (It's technically in a different transaction's scope)

Can't you just send the new records to a staging table? Then in a second data flow you read from that staging table, while joining to the global_id_pool table (in the SQL source statement), and then insert into the destination table?