I was wondering if someone could help me with creating a while loop to iterate through multiple databases(100 databases) and drop/Truncate the tables(Around 60 tables in each database) within those databases.Thank you. My task is to create an SSIS package to move the data from source database to target database.Data in the destination needs to be truncated as part of the process whenever I run that package the old data need to be truncated and the new data needs to be inserted. Kindly help. Thank you.
我想知道是否有人可以帮助我创建一个while循环来遍历多个数据库（100个数据库）并删除/截断这些数据库中的表（每个数据库中大约60个表）。谢谢。我的任务是创建一个SSIS包来将数据从源数据库移动到目标数据库。每当我运行该包时，需要截断目标中的数据作为进程的一部分，旧数据需要被截断并且新数据需要插入。请帮助。谢谢。
1 个解决方案
#1
0
I am just explain how i would go about this problem.
我只是解释如何解决这个问题。
Hoping the destination and source database,tables are of similar schema.
希望目标和源数据库，表具有类似的模式。
1) Try to create a table with all the database (Source and destination) details all 60 in a separate database.
1）尝试在一个单独的数据库中创建一个包含所有数据库（源和目标）详细信息的表。
2) You need get the database details using execute SQL task from the table and use For Each container for looping the logic of truncate the destination table and then move the data from source database to destination (The data is moved from source database one after other).
2）您需要使用表中的执行SQL任务获取数据库详细信息，并使用For Each容器循环截断目标表的逻辑，然后将数据从源数据库移动到目标（数据从源数据库一个接一个地移动）。
3) You will need to use Dynamic SQL and stored procedures for moving the data from source database to destination database (That you must be knowing). or You can use Data flow task too if you don't want to use Stored procedure or Dynamic Queries.
3）您将需要使用动态SQL和存储过程将数据从源数据库移动到目标数据库（您必须知道）。或者如果您不想使用存储过程或动态查询，也可以使用数据流任务。
Hope it helps you! :)
希望它能帮到你！ :)
#1
0
I am just explain how i would go about this problem.
我只是解释如何解决这个问题。
Hoping the destination and source database,tables are of similar schema.
希望目标和源数据库，表具有类似的模式。
1) Try to create a table with all the database (Source and destination) details all 60 in a separate database.
1）尝试在一个单独的数据库中创建一个包含所有数据库（源和目标）详细信息的表。
2) You need get the database details using execute SQL task from the table and use For Each container for looping the logic of truncate the destination table and then move the data from source database to destination (The data is moved from source database one after other).
2）您需要使用表中的执行SQL任务获取数据库详细信息，并使用For Each容器循环截断目标表的逻辑，然后将数据从源数据库移动到目标（数据从源数据库一个接一个地移动）。
3) You will need to use Dynamic SQL and stored procedures for moving the data from source database to destination database (That you must be knowing). or You can use Data flow task too if you don't want to use Stored procedure or Dynamic Queries.
3）您将需要使用动态SQL和存储过程将数据从源数据库移动到目标数据库（您必须知道）。或者如果您不想使用存储过程或动态查询，也可以使用数据流任务。
Hope it helps you! :)
希望它能帮到你！ :)