2

I am copying data from a source, an API, and copying it into Azure SQL DB. But in one of the column I am getting Json objects.

Any way i can use dynamic parameters (either through Pre-copy script or something else) in the pipeline to only take value of a particular tag from those json objects so that i can have only that value in the column. Only constraint is that I can't change the sink. It has to be Azure SQL DB.

Json object I am getting: [{"self":"https://xxxxxxxx.jira.com/rest/api/2/customFieldOption/11903","value":"Yes","id":"11903"}]

And I want only 'value' tag response not the complete json.

Gagan
  • 1,775
  • 5
  • 31
  • 59

1 Answers1

2

The pre-copy script is a script that you run against the database before copying new data in, not to modify the data you are ingesting.

What you can do if you cannot change the sink, is store the data in a different table, using a varchar field for the json. Then add another activity in your pipeline, where you take this data and store it in the actual table. In this second activity, you can use t-sql statements to modify the sqlQuery of the copy activity to extract the value you want from it.

This will be useful when designing the query: https://learn.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-2017

Hope this helped! :)

PS: in the second activity, use this to get the "value" 

select JSON_VALUE(fieldWhereYouStoredTheJson, '$[0].value') as jsonValue from temporaryTable
Martin Esteban Zurita
  • 3,161
  • 12
  • 23
  • 1
    But can i not use pre-copy script? And is it not possible to extract the value from the same pipeline like may be extract the value on the fly? – Gagan May 20 '18 at 01:57
  • 1
    Data factory doesnt store the data, it just moves the data between different stores and schedules data flows. It also doesnt transform it, that's why I propose that you use an intermediate table in your sql to modify it. – Martin Esteban Zurita May 21 '18 at 12:10
  • 1
    Thanks mate. Did that and it worked. I was just thinking of not creating a seperate table in the database. – Gagan May 22 '18 at 01:20
  • Anytime man! :) If you want to make it look as it doesn't use a table, you can create a table, use it as an intermediate table, and then drop it.. all orchestrated with data factory. I don't recommend this though, but maybe you want to keep the database cleaner. – Martin Esteban Zurita May 22 '18 at 14:10
  • 1
    @MartinEstebanZurita could anyone please help with this one? https://stackoverflow.com/questions/60859703/adf-copy-data-activity-check-for-duplicate-records-before-inserting-into-sql-d – newdeveloper Mar 26 '20 at 01:42
  • I read it too late, someone else already solved your problem ¯\_(ツ)_/¯ – Martin Esteban Zurita Mar 26 '20 at 13:01