Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SSIS 2005 - Update existing records in Data Flow Task

Status
Not open for further replies.

ReportDr00

IS-IT--Management
Mar 2, 2007
194
US

Hi,

In my dataflow task, i have source i.e sql server and lookup transformation and desitnation i.e sql server.
Based on Look up transformation if lookup does not find a record in reference table then insert new record into reference table and if lookup find record in reference table then update the record in reference table.

Here the reference table in lookup transformation will be the same that i will be inserting or updating the records.

Inserting new records is not a problem but how would i update the existing record easily?

Thanks
Armani
 
There are 2 approachs to updating existing data.

1) Using the OLE DB command you can execute an Update statement or a stored procedure if you have a lot of data this can be ab issue as it executes once for every row.

2) insert the records to be updated in to a table then use a stored procedure after the dataflow has completed to run the update using the table which is a set based update and will probably run faster.


However from your brief description I get the impression that you want to update the value and have the updated value available in the lookup task. if this is the goal then the only way is to use a custom script task where you would precache the values and apply the changes to the chached values.

The benefit of the lookup task is that by default it caches all records prior to the execution of the dataflow. You can change this and turn the caching off but then this will preform a select * from where for every record that passes through the task. even if you did this there is still the posibility that it would be wrong. This would not work in the following situation

You are importing 20K rows and your first record needs to update the lookup table if this was the only record that met that criteria that passed through the data flow no problem. However if your first row and your 5000th row were for the same lookup record the second record may not be aware of the updated value depending on the number of records that had been written or updated.

if you can give a better explination of the business goals and how the lookup is being used, business wise.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck
 

The Lookup reference table is also destination table for new and updated records.
Our business need is very simple, we have to get any new or updated record from a database on different server and move it to our dept server and database. The tables and fields are 99% same on both server. It is a smiple data flow task if update is not needed, but as we might need to update the existing records in destination it is making it bit complex

I like your option 2, but if you could post a sample stored procedure then i will build on to that and modify accordingly. I have not worked with stored procedure that much, but i could figure out the processing if i have one.

Thanks
Armani
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top