Data factory copying DB to DB - how to create new tables and copy the DB

Question

Hi,

I'm using "Azure.ResourceManager.DataFactory" version 1.0.0-beta.4 and I want to copy DB table from source to destination even if I don't have any table in the destination.

I want it to create table with the the desired schema and to copy data.

I found a way to do it with 'Create data tool' in the UI, but I need to do it in from my code that is written in C#

I tried many ways but It's not working.

I got an error : "Auto table creation in sink side is not applicable for your source dataset <My data set name>. Please create a new one"

Answer 1

@OGINTZ Marina I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others Opens in new window or tab", I'll repost your solution in case you'd like to accept the answer Opens in new window or tab.

Error Message:

• "Auto table creation in sink side is not applicable for your source dataset <My data set name>. Please create a new one"

Issue:

• How to programmatically develop an ADF copy activity pipeline that copies data from DB to DB and auto create tables if not exist.

Solution:

• Adding this line of C# code (both source & destination) helped resolve the issue - "Schema = DataFactoryElement<IList<DatasetSchemaDataElement>>.FromLiteral(new List<DatasetSchemaDataElement> { }),"

Here is the the complete code snippet:

public async Task CopyingDBtoDB()
{
	var dsSrc = new AzureSqlTableDataset(dataFactoryLinkedServiceReferenceSrc)
	{
        SchemaTypePropertiesSchema = DataFactoryElement<string>.FromLiteral("dbo"),
        Table = DataFactoryElement<string>.FromLiteral("<table name>"),
        Schema = DataFactoryElement<IList<DatasetSchemaDataElement>>.FromLiteral(new
			List<DatasetSchemaDataElement> { }),
    };
	DataFactoryDatasetData factoryDataSet = new DataFactoryDatasetData(dsSrc);            
	var resultds = await datasetCollection.CreateOrUpdateAsync(Azure.WaitUntil.Completed,    
		"<Source data set name>", factoryDataSet);  
	
	var dsDes = new AzureSqlTableDataset(dataFactoryLinkedServiceReferenceDes)
	{               
		SchemaTypePropertiesSchema = DataFactoryElement<string>.FromLiteral("dbo"),
		Table = DataFactoryElement<string>.FromLiteral("<desired table name>"),
        Schema = DataFactoryElement<IList<DatasetSchemaDataElement>>.FromLiteral(new 	
		   List<DatasetSchemaDataElement> { }), 
    };

    DataFactoryDatasetData factoryDataSetDes = new DataFactoryDatasetData(dsDes);
    await datasetCollection.CreateOrUpdateAsync(Azure.WaitUntil.Completed, "<Destination data set name>", 
		factoryDataSetDes);

	DataFactoryPipelineCollection collection = dataFactory.GetDataFactoryPipelines();       
   	string pipelineName = "<your pipeline name>";             
	DataFactoryPipelineData data = new DataFactoryPipelineData()             
	{                 
		Activities =                 
		{                     
			new CopyActivity("testing", new AzureSqlSource(),  new AzureSqlSink() 
				{TableOption = DataFactoryElement<string>.FromLiteral("autoCreate")}) 
            {           
				Inputs =                         
				{                             
					new DatasetReference(DatasetReferenceType.DatasetReference,"<Source data set name>")                             
                },                         
				Outputs =                         
				{                             
					new DatasetReference(DatasetReferenceType.DatasetReference,"<Destination data set name>")

                },    
             }  
       }                                                                                         					                                           
	}
}

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

I hope this helps the community!

Thank you again for your time and patience throughout this issue.

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

Answer 2

Ok, adding my answer to this issue.

basically what fixed the issue I had was to add : "Schema = DataFactoryElement<IList<DatasetSchemaDataElement>>.FromLiteral(new List<DatasetSchemaDataElement> { })," also in source and destination

public async Task CopyingDBtoDB()
{
	var dsSrc = new AzureSqlTableDataset(dataFactoryLinkedServiceReferenceSrc)
	{
        SchemaTypePropertiesSchema = DataFactoryElement<string>.FromLiteral("dbo"),
        Table = DataFactoryElement<string>.FromLiteral("<table name>"),
        Schema = DataFactoryElement<IList<DatasetSchemaDataElement>>.FromLiteral(new
			List<DatasetSchemaDataElement> { }),
    };
	DataFactoryDatasetData factoryDataSet = new DataFactoryDatasetData(dsSrc);            
	var resultds = await datasetCollection.CreateOrUpdateAsync(Azure.WaitUntil.Completed,    
		"<Source data set name>", factoryDataSet);  
	
	var dsDes = new AzureSqlTableDataset(dataFactoryLinkedServiceReferenceDes)
	{               
		SchemaTypePropertiesSchema = DataFactoryElement<string>.FromLiteral("dbo"),
		Table = DataFactoryElement<string>.FromLiteral("<desired table name>"),
        Schema = DataFactoryElement<IList<DatasetSchemaDataElement>>.FromLiteral(new 	
		   List<DatasetSchemaDataElement> { }), 
    };

    DataFactoryDatasetData factoryDataSetDes = new DataFactoryDatasetData(dsDes);
    await datasetCollection.CreateOrUpdateAsync(Azure.WaitUntil.Completed, "<Destination data set name>", 
		factoryDataSetDes);

	DataFactoryPipelineCollection collection = dataFactory.GetDataFactoryPipelines();       
   	string pipelineName = "<your pipeline name>";             
	DataFactoryPipelineData data = new DataFactoryPipelineData()             
	{                 
		Activities =                 
		{                     
			new CopyActivity("testing", new AzureSqlSource(),  new AzureSqlSink() 
				{TableOption = DataFactoryElement<string>.FromLiteral("autoCreate")}) 
            {           
				Inputs =                         
				{                             
					new DatasetReference(DatasetReferenceType.DatasetReference,"<Source data set name>")                             
                },                         
				Outputs =                         
				{                             
					new DatasetReference(DatasetReferenceType.DatasetReference,"<Destination data set name>")

                },    
             }  
       }                                                                                         					                                           
	}
}