如何使用SSIS Visual Studio 2008从多个SQL服务器为使用foreach循环的多个表构建动态数据库 [英] How to use SSIS Visual Studio 2008 to build Dynamic Datapull from multiple SQL servers for multiple tables using foreach loop
问题描述
我想做的是从不同的SQL源服务器（Server1，Server2，Server3）等构建动态数据拉。
What I want to do is build a dynamic data pull from different SQL source servers (Server1,Server2,Server3) etc.
要下拉到动态位置我的SQL服务器（Dev，Prod）转换为数据库（database1，database2等）
To pull down to dynamic locations on my SQL server (Dev,Prod) into databases (database1,database2,etc)
每次运行包时，这些表将被删除并重新创建，以确保我匹配的源服务器，如果他们更改任何来源（字段名称，数据类型，长度等）
The tables will be dropped and recreated each time the package is run so that I am sure I match the source servers if they change anything on source (field names, datatypes, lengths, etc)
我仍然会得到数据提取。我想使用 foreach循环中的单个数据流将其拉低。
I will still get the data to extract. I want to pull this down using a single dataflow in a foreach loop.
我有一张表，其中包含所有的服务器名称和表格和数据库，
I have a table that has all the server names and tables and databases in it and
我想循环遍历该表，并将表中的所有行从下拉到我的服务器（ server1.database1.table_x ， server5.database3.table_y 等）所以我不必为每个表构建一个新的数据流。
I want to loop through that table and pull all the rows of tables inside down to my server (server1.database1.table_x,server5.database3.table_y,etc) So that I don't have to build a new data flow for each table.
为了做到这一点，我已经使用将结果转储到对象中的sql任务来构建foreach循环。然后foreach循环使用具有7个不同字段的对象（ Source_Server_Name ， Source_Server_Type_Driver ， Source_Database ， Source_Table ， Source_Where_Clause ， Source_Connection_String ，然后是目的地的东西），并将每个这些字段放入一个不同的String变量中，以便在循环中使用。
In order to do this I have already built the foreach loop with a sql task that is dumping results into an object. Then the foreach loop takes that object that has 7 different fields (Source_Server_Name,Source_Server_Type_Driver,Source_Database,Source_Table,Source_Where_Clause,Source_Connection_String,then destination stuff) and it puts each of those fields into a different String variable for use inside the loop.
我可以使用变量动态更改连接，但是我无法弄清楚如何在数据流中获取列映射功能，
I can change the Connections dynamically using the variables but I can't figure out how to get the column mapping in the dataflow to function,
是否有某种脚本任务可以用来编辑后端的XML，这将为我创建列映射，以便元数据不会出错？任何帮助将不胜感激： - ）
Is there some kind of script task I can use to edit the backend XML that will create the column mapping for me so the metadata does not error out? Any help would be greatly appreciated :-)
这是最好的说明示例，我可以找到我正在做的只是记住我需要为每个不同的元数据设置表我拉到我的服务器。
This is the best illustrated example I could find of what I am doing just remember I need to have a different metadata setup for each table I pull down to my server.
http://sql-bi-dev.blogspot.com/2010/07/dynamic-database-connection-using-ssis.html
推荐答案
我最终使用的解决方案是BIML，它使用动态SQL和BIML即时生成包。不漂亮，但它有效： - ）
The solution I ended up using is BIML which generates the package on the fly using dynamic sql and BIML. Not pretty but it works :-)
这篇关于如何使用SSIS Visual Studio 2008从多个SQL服务器为使用foreach循环的多个表构建动态数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
