使用 SSIS 将超过 3 年的记录从一个数据库(生产数据库)移动到另一个数据库(存档数据库) [英] Move Records Which are older than 3 years from One Database (Production DB) to Another DB (Archive DB) Using SSIS
问题描述
要求是将超过 3 年的数据从生产数据库移动到存档数据库,一旦移动就从生产数据库中删除这些记录,因此在任何时间生产数据库都只有最后三年的记录.
Requirement is to Move Data Older than 3 years from Production DB to Archive DB , and Once Moved Delete those records from Production DB , so at any point of time Production DB will have only last three years of records.
我想通过 SSIS 实现这一点,我阅读了很多关于数据存档的文章,但无法找出最好的方法.
i want to achieve this by SSIS , i read quite a few articles about Data Archival but couldn't figure out the best Approch.
我是 SSIS 的新手
I am New to SSIS
我想实现完全像这样的东西(下面链接中给出的答案),并有额外的条件说只移动那些超过 3 年的记录,然后删除这些记录.
i want to achieve exactly something like this (answer given in Below link)with extra condition saying move only those records which are older than 3 years and then delete those records.
https://dba.stackexchange.com/questions/25867/moving-data-from-one-db-to-another-using-ssis
接受答案的标准应该解决
Criteria for an accepted answer answer should address
- 可扩展性
- 复杂性
- 故障处理
- 可靠性
推荐答案
可以使用OUTPUT子句一次性删除和返回要移动的数据.
You can use the OUTPUT clause to delete and return the data to be moved in one go.
create table ProductionTable
(
ValueDate datetime not null
, Data varchar(max) not null
)
insert ProductionTable values ('20100101', '3 years ago')
insert ProductionTable values ('20130425', 'this year')
insert ProductionTable values ('20130426', 'this year')
delete ProductionTable
output deleted.ValueDate, deleted.Data
where ValueDate <= dateadd(year, -3, getdate())
也可以在 SQLFiddle
现在我将向您展示在 SSIS 中重现示例所需的确切步骤:
Now I will show you the exact steps you need to follow in SSIS to reproduce the example:
- 创建一个新项目并为 ProductionDB 和 ArchiveDB 定义数据源.
- 在控制流"选项卡中,创建一个数据流任务".
- 在数据流"选项卡中,创建OLE DB 源"和OLE DB 目标".
- 在OLE DB Source"中,选择ProductionDB,选择SQL command"作为数据访问方式.使用输出子句粘贴删除语句.
- 点击列",然后点击确定.
- 在OLE DB Destination"中,选择 ArchiveDB 并选择表或视图 - 快速加载"作为数据访问模式,然后选择您的 ArchiveTable.
- 点击映射",然后点击确定".
- 运行该程序包,您应该能够验证一行已从 ProductionTable 中删除并移至 ArchiveTable.
希望有帮助.
要记住的其他事项,因为您要删除和移动数据,所以事务一致性非常重要.想象一下,在您的删除/移动中途,服务器出现故障,然后您最终会删除数据但没有将其放入存档.
Other things to keep in mind, because you are deleting and moving data around, transactional consistency is very important. Imagine half way through your delete/move, the server went down, you then end up with data being deleted but not made it to the archive.
如果您不确定如何通过执行事务一致性来保护您的数据,请向其他 SQL/SSIS 专家寻求有关如何在 SSIS 中使用事务的帮助.
If you are unsure about how to protect your data by enforcing transactional consistency, please seek help from other SQL/SSIS experts on how to use transactions in SSIS.
这篇关于使用 SSIS 将超过 3 年的记录从一个数据库(生产数据库)移动到另一个数据库(存档数据库)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!