SSIS - 循环使用100多个表名更新带有ORACLE数据的SQL Server表 [英] SSIS - Loop through 100+ table names updating SQL Server tables with ORACLE data
问题描述
Hello All！
Hello All!
以下是我要做的事情：
-Build ForEach循环容器循环遍历表由source / dest表名和查询语句组成
-Build ForEach Loop container to loop through a table consisting of source/dest table names and query statement
-Populate ssis_variables source_table，dest_table，sql_Query
-Populate ssis_variables source_table, dest_table, sql_Query
-Initialize housekeeping variables
-Initialize housekeeping variables
-Truncate dest_table
-Truncate dest_table
- 在OLE DB源中使用sql_query变量，在OLE DB Dest中使用dest_table变量
-Use sql_query variable in OLE DB source and dest_table variable in OLE DB Dest
-SQL dest table和ORA源表具有相同的布局，但表的列表是不同的
-SQL dest table and ORA source tables have same layout but the list of tables are different
因为OLE DB对象在初始表布局上绑定，循环中的下一个表将导致问题？
Is it true that since the OLE DB objects bind on the initial table layout that the next table in the loop will cause a problem?
有没有办法在没有每个表的单独数据流的情况下执行此操作？ （我认为截断在数据流之前必须是控制流中的SQL任务）
Is there a way to do this without separate dataflows for each table? (I think the truncate would have to be a SQL Task in the control flow before the dataflow)
如果我遗漏了任何信息并提前感谢，请告诉我！
Let me know if I have left out any info and thanks in advance!
推荐答案
嗨 William_Winters，
Hi William_Winters,
根据我的测试，如果我们在OLE DB源中使用sql_query变量并在OLE DB Dest中使用dest_table变量，我们将遇到错误像那样：
According to my test, if we use sql_query variable in OLE DB source and dest_table variable in OLE DB Dest, we will meet errors like that:
错误：0xC004706B在数据流任务，SSIS.Pipeline： "OLE DB目的地"验证失败并返回验证状态"VS_NEEDSNEWMETADATA"。
Error: 0xC004706B at Data Flow Task, SSIS.Pipeline: "OLE DB Destination" failed validation and returned validation status "VS_NEEDSNEWMETADATA".
错误：0xC004700C at数据流任务，SSIS.Pipeline：一个或多个组件验证失败。
Error: 0xC004700C at Data Flow Task, SSIS.Pipeline: One or more component failed validation.
错误：数据流任务中的0xC0024107：任务验证期间出现错误。
Error: 0xC0024107 at Data Flow Task: There were errors during task validation.
1.我们可以将数据从oracle加载到平面文件中，然后使用BCP命令将数据从平面文件加载到SQL Server。
1.We could load data from oracle into flat file ,then use BCP command to load data from flat file into SQL Server.
2。或者我们可以使用脚本任务来创建自定义数据流任务。欲了解更多信息，请点击
2.Or we could use Script Task to create customized Data Flow Task. For more information, please click
http://blogs.selectsifiso.com / programmatically-create-data-flow-task-in-ssis-package-using-c /
http://blogs.selectsifiso.com/programmatically-create-data-flow-task-in-ssis-package-using-c/
有人遇到过与你类似的场景，请点击
Someone came across similar scenarios as you, please click
https://social.msdn.microsoft.com/论坛/ sqlserver / zh-CN / 00773fb8-bcb3-4664-8a7f-24cebf4fd948 / dynamic-map-source-and-destination-table-in-ssis-package？forum = sqlintegrationservices
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/00773fb8-bcb3-4664-8a7f-24cebf4fd948/dynamically-map-source-and-destination-table-in-ssis-package?forum=sqlintegrationservices
我希望这个解决方案有用。
I hope this solution would be helpful.
最好的问候，
Mona Lv
这篇关于SSIS - 循环使用100多个表名更新带有ORACLE数据的SQL Server表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
