extra SQL tables

Question

Hi,

Is it possible to add more tables to an existing datasets in Azure data factory?
We have a pipeline that copy some on perm SQL tables to the Azure DB and everything works, but now I want to add some more tables to the data sets but dont see any option for this.
Any suggestion?

Answer 1

Hi @Shahin Mortazave ,

If you want to pull data into Azure SQL DB for a specific range, you have to create do the following:

1. create Datasets for your on-prem SQL table + your Azure SQL Table
2. create a pipeline with two parameters pullDataFrom and pullDataTo
3. add a CopyActivity into the pipeline where source --> Dataset_onPrem_Table and sink --> Dataset_Destination_Table
4. now, in the CopyActivity > source --> select Query option and write your select statement there. @markus.bohland@hotmail.de ('SELECT * FROM Table1 WHERE FORMAT(CREATION_DATE, ''yyyy-MM-dd HH:mm:ss'') >= ''', pipeline().parameters.pullDataFrom,
   ''' AND FORMAT(CREATION_DATE, ''yyyy-MM-dd HH:mm:ss'') <''', pipeline().parameters.pullDataTo,'''')
5. before pulling data on schedule, run the pipeline to pull and load all the records to the Azure SQL Table by widen the pullDataFrom and pullDataTo date until your schedule the pipeline.
6. in the trigger, you can use the following expression to dynamically generate the daterange e.g., @markus.bohland@hotmail.de (formatDateTime(adddays(utcnow(), -1), 'yyyy-MM-dd'), ' 00:00:00')
   @markus.bohland@hotmail.de (formatDateTime(utcnow(), 'yyyy-MM-dd'), ' 00:00:00')

Please see the screenshots for details:

----------

If the above response is helpful, please accept as answer and up-vote it. Thanks!

Answer 2

Hi @Shahin Mortazave ,

In the Azure Data Factory, when you hover over the right-side on the Dataset, you will see Action (...) --> click on the action and you will find New Dataset. Thanks!

----------

If the above response is helpful, please accept as answer and upvote it. Thanks!

Answer 3

@Nasreen Akter Thank you for your reply,
I have 2 questions,

1. If I understood you correctly to add extra tables to the same pipeline I have to create a new dataset, am I right?
2. When I did create my pipeline to copy data from some of the tables of my on-perm DB, I use the scheduled option to run every 24 hours, is this means that every 24 hours the tables get copy all over again or only changes will be get copied?

Thanks

Answer 4

@Nasreen Akter Thank you for the nice info,

As I meantioned I have already a pipeline that pull data from 4 tables of our on-perm SQL and load to the Azure al tabels to Azure SQL Tables.

As I understand I have to first add this query to my source dataset, but can I put the name of all of 4 tabels in this query?

@markus.bohland@hotmail.de ('SELECT * FROM Table1 WHERE FORMAT(CREATION_DATE, ''yyyy-MM-dd HH:mm:ss'') >= ''', pipeline().parameters.pullDataFrom,
''' AND FORMAT(CREATION_DATE, ''yyyy-MM-dd HH:mm:ss'') <''', pipeline().parameters.pullDataTo,'''')

Do you have a document that eplain the steps? becuase I cannot find the dynamic contant and input in your screenshots.

Thanks