c# Copy SQL table data to another DB with Where clause filter using Data Factory

Question

I am in a process to copy data from one SQL database (Source) and move to another SQL Database (destination) through data factory using c# code. I am able to copy all the data from a source table to destination table, but i want to move filtered data only, like SELECT * FROM Source.tbl WHERE Category = 5. There would be around 10-15 table that i would move data. Can you provide me sample code which may help me?

My code for moving single table all data..

// Authenticate and create a data factory management client var context = new AuthenticationContext("https://login.windows.net/" + tenantID); ClientCredential cc = new ClientCredential(AppID, AuthKey); AuthenticationResult result = context.AcquireTokenAsync("https://management.azure.com/", cc).Result; ServiceClientCredentials cred = new TokenCredentials(result.AccessToken); 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;
        }

Fang Liu Fang Liu · Accepted Answer · 2018-08-07T12:07:02

You could put your query into the SqlReaderQuery property of your sql Source.

c# Copy SQL table data to another DB with Where clause filter using Data Factory

2 Answers