使用SSIS将每个表的数据从服务器A动态复制到服务器B [英] Copy data of each table from server A to server B dynamically using SSIS

问题描述

我的任务是在SSIS中创建工作流，该工作流将把每个表的数据从服务器A复制到服务器B的相同表中。现在，我已经停止了从A服务器获取数据并复制的步骤到服务器B。到目前为止，我已经创建了工作流，其步骤如下：

My task is to create workflow in SSIS where it will be copying data of each table from server A to the same tables in server B. For now, I have stopped in step where I'm taking data from A server and copy it to server B. Till now I have created workflow where steps are as below:

1. 从具有以下名称的Excel文件中读取数据：要处理的表


2. 将此行插入目标数据库（服务器B）中以备将来使用


3. 在以上步骤中连接到下一个对象的控制流中-执行SQL
   
4. 创建了另一个变量 localTable，在其中存储 GlobalListOfTables的每一行变量/集，我稍后会在 FOR LOOP中使用它。

1. Read data from Excel file where there are placed names of tables to be processed
2. Insert this rows in destination database (server B) for future
3. In Control Flow connected above steps to next object - Execute SQL task where inside I'm taking all loaded names from table to global project variable named as "GlobalListOfTables".
4. Created another variable "localTable" where I'm storing each row of "GlobalListOfTables" variable/set which I'm using it later in "FOR LOOP".

在这里，我已经停止了，我想做的是：

Here I have stopped and what I want to do is:

1. 在此LOOP中从每个表的源服务器获取所有数据使用创建的 localTable变量，然后将所有数据插入到目标服务器中，并将其插入到与 localTable变量相同的表名中。

最后一点5使我有些困惑，因为桌子之间会有所不同，所以没有一种桌子打孔的方式。

This last point 5 makes me a little bit confused because tables are gonna be different from each other so there is no one pattern of tables struckture.

有人能让我知道吗我应该在SSIS中做些什么，应该从可用列表中取得哪个控制权以达到第5点？

Can someone let me know how should I do in SSIS and which control should I take from available list to achieve point 5?

推荐答案

您可以使用C＃SMO脚本任务中的对象进行动态表列表的传输。不需要SSIS循环。 SSIS对象变量（ GlobalListOfTables ）将需要包含在脚本任务的 ReadOnlyVariables 字段中。除了下面列出的内容之外，请确保将Microsoft.SqlServer.SmoExtended和Microsoft.SqlServer.ConnectionInfo引用添加到脚本任务。

You can use C# SMO objects from a Script Task to do the transfer for a dynamic table list. The SSIS loop won't be necessary. The SSIS object variable (GlobalListOfTables) will need to be included in the ReadOnlyVariables field on the Script Task. Make sure to add Microsoft.SqlServer.SmoExtended and Microsoft.SqlServer.ConnectionInfo references to the Script Task in addition to those listed below.

using System.Data;
using Microsoft.SqlServer.Management.Smo;
using System.Collections.Generic;
using System.Data.OleDb;



       string databaseName = "DatabaseName";
       List<string> tableNames = new List<string>();
       DataTable dt = new DataTable();
       OleDbDataAdapter dataAdapter = new OleDbDataAdapter();

        //get table names from SSIS object variable
       dataAdapter.Fill(dt, Dts.Variables["User::SourceServerName"].Value);

        //populate list
        foreach (DataRow dr in dt.Rows)
        {
          tableNames.Add(dr[0].ToString());
        }

        //create source server object
        Server srcServ = new Server(@"SourceServerName");
        srcServ.ConnectionContext.LoginSecure = true;
        srcServ.ConnectionContext.StatementTimeout = 600;
        srcServ.ConnectionContext.Connect();

        //define source database as smo object
        Database sourceDatabase = srcServ.Databases["SourceDatabaseName"];

        Transfer transfer = new Transfer();
        transfer.Database = sourceDatabase;

        //set destination server and database
        transfer.DestinationServer = @"DestinationServerName";
        transfer.DestinationDatabase = databaseName;

        //overwrite objects if they exist
        transfer.DropDestinationObjectsFirst = true;

        transfer.CopyAllObjects = false;
        transfer.CopySchema = true;

        //include data
        transfer.CopyData = true;

        foreach (Table t in sourceDatabase.Tables)
        {   
            //extract table names that were originally in SSIS object variable and avoid system objects
            if (tableNames.Contains(t.Name) && !t.IsSystemObject)
            {
                transfer.ObjectList.Add(t);
            }
        }
        //transfer objects
         transfer.TransferData();

这篇关于使用SSIS将每个表的数据从服务器A动态复制到服务器B的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！