Update on Insert

Question

Please read Update1 first! This is SSIS specific question.

I have the following tasks:

1. I need to periodically move data from table A in MySQL to Table B in MsSQL.
2. Then all of the moved rows needs to be updated in Table A (change a single column value).

I have accomplished task 1 by creating the following Data Flow: ADO NET Source -> Data Conversion -> SQL Server Destination. It's working great. I run this query X minutes.

Now, using SSIS tool, how do I update the rows that I just "Data Flow"'ed in MySQL? If I was to use a plain SQL I'd do (in MySql):

update table mytable set status="moved" WHERE ...

(this will make sure that next time task 1 pulls the data out - it skips all rows that were already "moved")

So the problem is that I don't know how to connect WHERE clause in the 2nd task with the resultset of the 1st task.

Update1: I'm not so interested in optimizing the update procedure. I have simplified it here to put emphasis on the the question: How to implement this in SSIS. I'm specifically interested in what kind of Data/Control Flow blocks in SSIS I need to use what is the sequence.

Answer 1

select max(id) from a

[copy rows to b]

update a set moved = 1 where id <= ?

Answer 2

One way to do this type of thing:

1. Copy the data to be moved to a temp table on the source system

2. Move the data from the temp table (source) to your target system like you do now

3. Update the master source table where the Ids are in the temp table

4. drop the temp table

Ron

Answer 3

I find that the easiest is to have an intermediate status like:

0 = not moved, 1 = scheduled to move,  2 = moved

• So you first flag rows at source with status = 1
• Then move those rows over
• When transaction succeeds, UPDATE myTable SET status = 2 WHERE status = 1

AFTER YOUR UPDATE:

You could use Multicast just before your destination and capture IDs of records transferred over into another table CapturedRows in the source DB. After that use Execute SQL Task to update rows in a source table, like:

UPDATE myTable SET status = 'moved' WHERE ID IN (SELECT ID FROM CapturedRows)

After this you would use another Execute SQL Task to truncate the CapturedRows table.

You could also connect OLE DB Command to the Multicast directly, to update records one by one, as they flow -- but that would be quite slow.

To use this, you would have to set Fail Package On Failure for the Data Flow Task to make sure that it stops if an insert fails and Transaction Option for package to required and for the Data Flow to supported.

Answer 4

define an on-update trigger for table-a that inserts the record into table-b when table-a.status is being changed to "moved".