Azure Data Factory - Copy Activity ( Copy Azure Function response to CSV)

Question

Hi,

Use Case: Read data from SQL server, transform each row by passing it to Azure Function and then load them into Cosmos DB.

1. SQL Server with Integration Run time is done.
2. Azure Function to transform each row is complete.
3. Cosmos DB sink is also ready.

EDIT HERE (Added points) 4. Transformed data should be upserted back to the SQL server against each row that was read in the first step.
5. Transformed data should also be saved onto a CSV file in Blob Storage.

I'm unable to figure out how to load the response ( JSON Object) from the Azure function into the cosmos sink.

Pipeline:

Read Data from SQL using Lookup activity ----> Use a ForEach iteration to iterate through each row -----> Add Azure function as an activity inside ForEach Iteration and pass each item() ----> Azure Function is responding back with transformed data as JSON object -----> ???? (I'm stuck here) How do I now load it back into the Cosmos Sink.

Can someone guide me out here?

Answer 1

There are a few ways you can accomplish this.

Before loop copy data to storage in csv format
Lookup against the csv files
In your loop - execute function to populate Cosmos DB
After loop Copy csv files to staging table in SQL.
Run stored procedure to merge the staged data into the final table

One thing to note is that Lookups only support a max of 5000 records (or 4MB in size), so make sure you are not trying to select more than that.

Answer 2

@Vigneshwar S B You cannot directly use the function activity output in the pipeline. As suggested earlier you can try inserting from function itself to Cosmos DB or you can try the below - Instead of using function you can try using Copy data Activity with REST source with Azure Function URL as linked service. You need to pass the function url with key as a Base URL. (See screenshot below).

I have tested this and it gets the data from function and pushes it to cosmos db.


This will cover you 1st part.