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

Vigneshwar S B 26 Reputation points
2021-03-05T03:35:47.107+00:00

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?

Azure Functions
Azure Functions
An Azure service that provides an event-driven serverless compute platform.
4,610 questions
Azure Cosmos DB
Azure Cosmos DB
An Azure NoSQL database service for app development.
1,528 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,085 questions
{count} votes

Accepted answer
  1. John Aherne 81 Reputation points
    2021-03-10T04:31:58.843+00:00

    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.


1 additional answer

Sort by: Most helpful
  1. Saurabh Sharma 23,786 Reputation points Microsoft Employee
    2021-03-10T01:27:20.343+00:00

    @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).
    76030-image.png
    I have tested this and it gets the data from function and pushes it to cosmos db.
    76033-image.png
    76041-image.png
    This will cover you 1st part.