Update Destination from Source in SSIS Data Flow Task

2
votes

I am developing a job that runs every 15 seconds that will give a "realtime" view of some of our data.

It pulls from a source on a different server that looks at a particular application.

In the past, we truncated the destination and just inserts. However, I would like to Update the destination with the new source, so we don't see a count of 4000 dip to 0, then back to 4003.

One solution is to have a temp table, then have a second task update the destination table from that temp table.

Is this the preferred way? This feels dirty.

I notice that within the Data Flow Task OLE DB Destination, I can change data access mode to SQL Command. Any idea where I can reference the source from this command? It seems logical that the data is somewhere on our side that we can manipulate.

Thanks for the assistance!

1
sqlsql-serverssis
Do you need to update changes to the rows or simply insert new rows? – Zane
Yes; I should specify -- there will only be abound 10-15 records in that table. I will simply be updating them. – Ehren

1 Answers

2
votes

If you're only doing a few records why not use a lookup to match to what you already have then either insert or update.

OleSource To Lookup

QueryToMatch

Then on no match do a BULK INSERT and on a match do an UPDATE.