使用 SSIS 将数据从多个数据库加载到另一台服务器 [英] Loading data from multiple db to another server using SSIS

问题描述

我使用 SSIS 包将表列和数据从一个数据库移动到另一个数据库.

I have used SSIS package to move table columns and data from one database to another database.

服务器 A、数据库 A、表 A

SERVER A, Database A, Table A

服务器 B、数据库 B、表 B(但仅限于从表 A 中选择的列)

SERVER B, Database B, Table B (but only with selected columns from Table A)

我已将数据流任务与 OLEDB 源和 OLEDB 目标一起使用.派生列以及在 SERVER B 的表 B 中创建新日期列.我需要自动执行此操作以使用 SQL 作业将 50 个数据库表 A 从 SERVER A 加载到表 B(使用表 A 中的选定列)到服务器 B.请有人建议我如何将数据库加载为循环.

I have used Data flow task with OLEDB source and OLEDB destinations. Derived columns as well to create new date columns in Table B in SERVER B. I need to do this automatically to load 50 databases Table A from SERVER A to Table B (with selected columns from Table A) into SERVER B using SQL Job. Please can anyone suggest me how to nake database load as a loop.

谢谢.

推荐答案

我将向您展示如何使用 SSIS 从 DatabaseA 的 50 个表中获取所有列并将它们加载到 DatabaseB 中.那个包看起来像这样.

I'm going to show you how to grab ALL the columns from the 50 tables of DatabaseA and load them into DatabaseB using SSIS. That package will look something like this.

我将解释逻辑流程.

第一个执行 SQL 任务将从信息架构中获取表列表.

The first Execute SQL task is going to get a list of tables from the Information Schema.

TABLE_SCHEMA|TABLE_NAME
MySchemaA   |MyTableA1
MySchemaA   |MyTableA2

我们将获取该列表并将其保存为 ResultSet 变量.保存后，我们可以使用 Foreach 循环 ADO 枚举器任务循环遍历 ResultSet.对于每个循环，我们将把值从一行映射到 TABLE_SCHEMA 和 TABLE_NAME SSIS 变量.然后，我们将在动态 SQL 语句中使用这些值.第二个执行 SQL 任务是从 SQLStatement 变量中获取它的命令.我们已经通过以下表达式设置了该变量...

We're going to take that list and save it as a ResultSet variable. Once saved, we can loop over the ResultSet using a Foreach Loop ADO Enumerator task. For each loop we're going to map values from one row into the TABLE_SCHEMA and TABLE_NAME SSIS variables. Then, we will use those values in dynamic SQL statement. The second Execute SQL task is getting it's command from the SQLStatement variable. We've set that variable by the following Expression ...

SELECT * INTO DatabaseB."+ @[User::TABLE_SCHEMA] + "."+ @[User::TABLE_NAME] +" FROM DatabaseA."+ @[User::TABLE_SCHEMA] + "."+ @[User::TABLE_NAME]

所以对于每个循环，将schema和表名注入到SQL语句中，表是SELECT * INTO同名的目的表.

So for each loop, the schema and table name is injected into the SQL statement, and the table is SELECT * INTO a destination table of the same name.

查看这里 和 此处有关如何使用 Foreach 循环 ADO 枚举器映射变量的更详细示例.

Look here and here for more detailed examples about how to use the Foreach Loop ADO enumerator to map the variables.

现在...

我确实读到了你想要的

从表 A 中选择列

如果您足够幸运，需要从 DatabaseA 中的 50 个表中的每一个中获得完全相同的列集，那么只需将上面查询中的 * 更改为 [ColumnA],[ColumnB],[C列].如果您碰巧需要为每个表使用不同的列集，那么……那可能会有问题.您需要创建一个 TABLE_NAME | 列表.ColumnSet 并将其集成到您的循环中……嗯……听起来好像有很多工作要做！在编写代码之前，您可能可以手动完成 50 个.希望这会有所帮助！

If you're lucky enough to need the exact same columnset from each of your 50 tables in DatabaseA then, just change the * in the query above to [ColumnA],[ColumnB],[ColumnC]. If you happen to need a different columnset for each table, well... that may be problematic. You'd need to create a list of TABLE_NAME | ColumnSet and integrate that into your looping... and well.. thats starting to sound like a lot of work! You could probably do the 50 by hand before you could code it. Hope this helps!

这篇关于使用 SSIS 将数据从多个数据库加载到另一台服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！