March 16, 2011 at 1:01 pm
Hi I got to desgin an ssis package whihc pulls data from excel sheet to a db. So i designed that package. Now i need to modify the package such that when i run the package after a week it should update all the records that are updated in the excel sheet for that week .
For example
I got an excel sheet with records
Date name Age Id
03/16/11 pet 21 4
so now i ran the package and copied that data to my table
now after 1 week the excel sheet got updated as
Date name Age Id
03/16/11 pet 21 4
03/23/11 sam 22 5
Now i should modify the package in such a way that i should check the execl sheet for the date and update the complte new date record like it will check the above excel sheet and finds 03/23/11 as a new date and updates the table with that new record.
March 17, 2011 at 10:00 am
I'm not sure if the T-SQL (SS2K8) forum is the correct place for this topic or that the Lookup transformation task is the desired task. Perhaps the Merge Join transformation is what you need.
Without the merge join transformation, you want to do an upsert. This is possible using the merge statement in TSQL (please refer to books online for more documentation).
- create 2 tables -- a staging table and a final table
- delete all records from the staging table
- insert the new data set into the staging table
- use the merge statement to insert into the final table
Hope that this helps.
Thanks...Chris
March 17, 2011 at 2:33 pm
Agree with Chris, your best option is to always insert the excel sheet in a staging table and MERGE it with the actual table doing update when matched and inserting whats not matched
--
Thiago Dantas
@DantHimself
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply