0

I plan to move data from a number of databases periodically using Azure Data Factory (ADF) and i want to move the data into Azure Parallel Data-Warehouse (APDW). However the 'destination' step in the ADF wizard offers me 2 functions; 1- in the case where data is retrieved from a view you are expected to map the columns to an existing table, and 2- when the data comes from a table you are expected to generate a table object in the APDW.

Realistically this is too expensive to maintain and it is possible to erroneously map source data to a landing zone.

What i would like to achieve is an algorithmic approach using variables to name schemas, customer codes and tables.

After the source data has landed i will be transforming it using our SSIS Integration Runtime. I am wondering also whether a SSIS package could request source data instead of an ADF pipeline.

  1. Are there any resources about connecting to on premises IRs through SSIS objects?
  2. Can the JSON of an ADF be modified to dynamically generate a schema for each data source?
Jimbo
  • 3
  • 5
  • For ADF Wizard "1- in the case where data is retrieved from a view you are expected to map the columns to an existing table, and 2- when the data comes from a table you are expected to generate a table object in the APDW." 1 is right, because ADF doesn't support auto generate table for views. 2 is not exactly right. For table, you could also map it to existing table. You could use the toggle link to below the table name to toggle existing table/ new table. – Fang Liu Jul 03 '18 at 15:13

1 Answers1

0

For your question #2, Can the JSON of an ADF be modified to dynamically generate a schema for each data source:

You could put your generate table script in precopyscript.

Fang Liu
  • 2,325
  • 2
  • 13
  • 18
  • I see what you are saying about preCopyScript, the definition of this object does suggest that it could be used for such. However, ultimately what i want is a programmatic approach which uses variables and iterates per customer. For that to happen the logic would need to be at a higher level than a single pipeline and as far as i know it is currently not possible to parameterise a pipeline for multiple integration-runtimes. – Jimbo Jul 04 '18 at 09:55
  • I think maybe you could use foreach activity + copy activity, and make preCopyScript as a parameters? – Fang Liu Jul 04 '18 at 09:58