I have two DBs and would like to merge them. Thanks .net I made migration which helped me easly merge second database to first. Now I need to migrate data from old(second) DB to first DB. Via Tasks -> Export data(in SSMS) I created such migration, but due the order of migrating tables got the problem. Some tables must be migrated first because others require foreign key on them. There is no such ability in SSMS to set order, so I created .dtsx package and opened in Visual Studio to modify it (Integration Services Project). But here I also don't see the option to set the order of migrating.
Issue like Check constraints off is not suitable.
Also about writing my own query to migrate: guess in case migrating thousands of tables writing queries to migrate is not acceptable.
1条答案
按热度按时间bybem2ql1#
SSIS is a powerful tool for migrating data between databases but it doesn't automatically handle table precedence based on foreign key relationships. However, there are third-party tools available that can help you achieve this. One them are: Redgate Data Compare or ApexSQL Data Diff.
In SSIS, you typically need to manually set table precedence for data migration. However, for thousands of tables, you can use Biml (Business Intelligence Markup Language). Biml is an XML-based language that can programmatically generate SSIS packages, providing a more efficient way to handle complex data migrations.
You can also take into account reorganising the database script in T-SQL, setting the precedence manually.
If it weren't the case of merging data, it would be very useful to use *.bacpac files to migrate data ("Tasks" > "Export Data-tier Application in SSMS") or even restoring from a backup.