How to get dynamically all json files table data in a table(sql server data warehouse) using Azure Data Factory(Load from ADF to DWH)

Question

I have to get all json files data into a table from azure data factory to sql server data warehouse.i'm able to load the data into a table with static values (by giving column names in the dataset) but generating in dynamic i'm unable to get that using azure data factory.Can some help on this solution to get dynamically in azure data factory? Many thanks in Advance.

json file data as follows:

{ "TABLE": "TEST_M1", "DATA": [{ "DFG": "123456", "ADF": "SFSDF" }, { "DFG": "ABADHDD", "ADF": "GHB" } }

same as follows for different TABLE names(TEST_M2.....)

are you saying the json structure is different for each file? — Stephen Lloyd, Apr 18 '19 at 16:37
@ Stephen-Thanks for your reply.Json structure will be same but data will be changed — pythonUser, Apr 22 '19 at 12:57
@Stephen-Thanks for your reply .The json file is stored in our local server and we are accessing it through FTP. — pythonUser, Apr 24 '19 at 11:09

score 0 · Answer 1 · answered Apr 25 '19 at 03:05

0

You could invoke a stored procedure script in sql serer sink when doing copy. Stored procedure script defines the logic about how to generate dynamic value based on source json data. See an example: https://learn.microsoft.com/en-us/azure/data-factory/connector-sql-server#invoking-stored-procedure-for-sql-sink

answered Apr 25 '19 at 03:05

Wang Zhang

317
1
3

Thanks for your reply.But i'm trying to do this in Sql server Data warehouse.Can you please give alternate solution data warehouse? – pythonUser Apr 25 '19 at 08:37

How to get dynamically all json files table data in a table(sql server data warehouse) using Azure Data Factory(Load from ADF to DWH)

1 Answers1