Is there a way to use derived and cast option alternative in Copy Activity?

Question

I am having an ADF pipeline to read a big Json document from cosmos and move to sql database.

1. The Json have datetime that needs to be changed to datetime2 for SQL mapping.
2. The json have a value that is in string but needs to be converted to int for SQL (eg. is type is A in json then sql value is 1,If type is B then SQL value is 2)

3.The json have sub json structure that needs to be mapped to Nvarchar(max) in SQL

What I tried :

Tryout 1:

I tried using DataFlow which allowed cast and derived to sort the above 1 and 2 issue but the DataFlow does not allow a subjson to be converted as String.

DataFlow

Tryout 2:

I tried using copy activity and was able to convert the sub json to string for Issue 3 but that does not have option for casting and Derived Columns as DataFlow . (This was from my another question )

Question:

Can I have dataFlow and pass its input to copy activity ? or some way to perform cast and Derived in Copy Activity ?

Answer 1

You can use Dataflow for the 3rd case as well.

This is my starting cosmos schema:

I have created a SQL table with 3 columns documentType,content and date. The content column type is nvarchar(max).

I have used the same expression as yours toString(content) in the derived column.

I have added the sink table and executed the dataflow from pipeline. You can see the complex JSON converted to nvarchar() type in the result SQL table.

You have mentioned that you are getting error on the SQL sink when converting it. I suggest you to first run the dataflow from the pipeline and check if its results same or not as It's giving the expected results when running from pipeline.