c# 使用数据工厂将 SQL 表数据复制到另一个具有 Where 子句筛选器的数据库

Question

我正在使用 C# 代码通过数据工厂从一个 SQL 数据库(源(复制数据并移动到另一个 SQL 数据库(目标(。 我能够将所有数据从源表复制到目标表，但我只想移动过滤后的数据，例如选择 * FROM Source.tbl 其中类别 = 5。我会移动大约 10-15 个表。你能给我提供可能对我有帮助的示例代码吗？

我用于移动单个表所有数据的代码..

对数据工厂管理客户端进行身份验证并创建 var context = new AuthenticationContext("https://login.windows.net/" + tenantID(; ClientCredential cc = new ClientCredential(AppID， AuthKey(; 身份验证结果结果 = 上下文。AcquireTokenAsync("https://management.azure.com/"， cc(.结果; ServiceClientCredentials cred = new TokenCredentials(result.访问令牌(; var client = new DataFactoryManagementClient(cred( { SubscriptionId = SubscriptionID };

        // Create data factory
        Factory dataFactory = new Factory { Location = Region, Identity = new FactoryIdentity() };
        // This line throws error, we cannot proceed further. unless we get access of creating DF or update or access.
        client.Factories.CreateOrUpdate(ResourceGroup, DataFactoryName, dataFactory);
        var DF = client.Factories.Get(ResourceGroup, DataFactoryName);
        while (DF.ProvisioningState == "PendingCreation")
        {
            System.Threading.Thread.Sleep(1000);
        }
        LinkedServiceResource storageLinkedService = new LinkedServiceResource(
            new AzureSqlDatabaseLinkedService
            {
                ConnectionString = new SecureString(SourceSQLConnString)
            }
        );
        client.LinkedServices.CreateOrUpdate(ResourceGroup, DataFactoryName, SourceSQLLinkedServiceName, storageLinkedService);
        LinkedServiceResource sqlDbLinkedService = new LinkedServiceResource(
            new AzureSqlDatabaseLinkedService
            {
                ConnectionString = new SecureString(DestSQLConnString)
            }
        );
        client.LinkedServices.CreateOrUpdate(ResourceGroup, DataFactoryName, DestSQLLinkedServiceName, sqlDbLinkedService);
        DatasetResource SourceSQLDataSet = new DatasetResource(
            new AzureSqlTableDataset
            {
                LinkedServiceName = new LinkedServiceReference
                {
                    ReferenceName = SourceSQLLinkedServiceName
                },
                TableName = Table, 
            }
        );
        client.Datasets.CreateOrUpdate(ResourceGroup, DataFactoryName, SourceSQLDataSetName, SourceSQLDataSet);
        // Create a Azure SQL Database dataset
        DatasetResource DestSQLDataSet = new DatasetResource(
            new AzureSqlTableDataset
            {
                LinkedServiceName = new LinkedServiceReference
                {
                    ReferenceName = DestSQLLinkedServiceName
                },
                TableName = Table
            }
        );
        client.Datasets.CreateOrUpdate(ResourceGroup, DataFactoryName, DestSQLDataSetName, DestSQLDataSet);
        PipelineResource pipeline = new PipelineResource
        {
            Activities = new List<Activity>
            {
                new CopyActivity
                {
                    Name = "CopyFromSQLToSQL",
                    Inputs = new List<DatasetReference>
                    {
                        new DatasetReference()
                        {
                            ReferenceName = SourceSQLDataSetName
                        }
                    },
                    Outputs = new List<DatasetReference>
                    {
                        new DatasetReference
                        {
                            ReferenceName = DestSQLDataSetName
                        }
                    },
                    Source = new SqlSource(),
                    Sink = new SqlSink { }
                }
            }
        };
        client.Pipelines.CreateOrUpdate(ResourceGroup, DataFactoryName, PipelineName, pipeline);
        // Create a pipeline run
        CreateRunResponse runResponse = client.Pipelines.CreateRunWithHttpMessagesAsync(ResourceGroup, DataFactoryName, PipelineName).Result.Body;
        // Monitor the pipeline run
        PipelineRun pipelineRun;
        while (true)
        {
            pipelineRun = client.PipelineRuns.Get(ResourceGroup, DataFactoryName, runResponse.RunId);
            if (pipelineRun.Status == "InProgress")
                System.Threading.Thread.Sleep(15000);
            else
                break;
        }

Answer 1

您可以将查询放入 sql 源的 SqlReaderQuery 属性中。

Answer 2

我与数据工厂支持人员交谈，他们说我们尚未实现， 创建数据工厂， 创建链接服务 循环中创建数据集和创建复制活动