SSIS and updating information - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

SSIS and updating information

Author

Topic

manared
Starting Member

10 Posts

Posted - 2007-03-08 : 14:37:19

I'm using sql 2005 and used SSIS to import two Access 97 databases into one sql database. I want to keep updating the info, but when I import again, it just appends everything to the sql database. How do I make it so that it only appends any new information or have it delete the tables and then re-add them again so I have all new, updated information? I was also wondering if I could then have a stored procedure or something that does this and runs like twice a day? If I can, how would I do that?

Antonio
Posting Yak Master

168 Posts

Posted - 2007-03-09 : 07:00:06

quote:
Originally posted by manared

I'm using sql 2005 and used SSIS to import two Access 97 databases into one sql database. I want to keep updating the info, but when I import again, it just appends everything to the sql database. How do I make it so that it only appends any new information or have it delete the tables and then re-add them again so I have all new, updated information? I was also wondering if I could then have a stored procedure or something that does this and runs like twice a day? If I can, how would I do that?

First of all your source tables should have a unique identifier (timestamp field or an identity column).

Suppose you have an Access 97 source table A which contains a tamestamp field and an empty SQL Server 2005 destination table.

You retrieve the maximum (ID or timestamp from your destination table - default to 0 if the return value is NULL). You then use q sql command in your data source component to retrieve all records from your source table where the ID/timestamp > max(source table ID/timestamp). That way you ensure that you're always picking new records from your source data.

Of course you could always truncate your destination table and re-import the entire source data but that could be ineffiencent if you're table size is large.

IF you wish to run this process twice a day then you could shedule a SQL Server Job (using SQL Server Agent) -- referr to Books On Line or the SSIS MSDN forum for furher info.

_________________________________________________________________________________________________________________________
Inability is a disaster; patience is bravery; abstinence is a treasure, self-restraint is a shield; and the best companion is submission to Divine Will.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources