Azure Synapse Data Flow/Copy Activity Sink to REST with id in URL

ralphjjr 101 Reputation points
2021-11-20T22:06:07.707+00:00

Is it possible to sink to a REST endpoint whereby you add the record id of each row in the URL of the REST API call?

Say i have 10 rows to sink. I can't do this to just one endpoint:

/someapi/update
{
payload of 10 rows
}

I need to do this:

/someapi/update/id1
{
payload
}

/someapi/update/id2
{
payload
}

for each id.

Is that possible natively - my only real idea is to create a loop in a pipeline and call a web activity N times...

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
2,608 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
6,269 questions
{count} votes

Accepted answer
  1. ralphjjr 101 Reputation points
    2021-11-29T23:27:58.087+00:00

    I discovered the issue.

    I was trying to only use an Insert Method, and set Delete, Upset and Update to NONE in the sink settings.
    The Sink did not want that. Each time I closed the Data Flow and reopened it would reset all the methods.
    That is what was causing the issue.

    I just added an Alter Row transformation before the sink. That fixed it. It is now working as expected.


2 additional answers

Sort by: Most helpful
  1. ShaikMaheer-MSFT 28,096 Reputation points Microsoft Employee
    2021-11-22T09:45:41.963+00:00

    Hi @ralphjjr ,

    Welcome to Microsoft Q&A Platform. Thank you for posting query here.

    Yes, you should have loop and for each iteration dynamically form your API URL and make call using web activity.

    Dataflow/Copy activity does not have any internal looping feature. Hence, above approach will work fine.

    Hope this will help. Please let us know if any further queries.

    ------------------------------

    • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
    • Want a reminder to come back and check responses? Here is how to subscribe to a notification
    • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

  2. MarkKromer-MSFT 5,136 Reputation points Microsoft Employee
    2021-11-22T19:08:24.163+00:00

    Use the "Row relative URL" property in the Data Flow sink for a REST sink type. ADF will automatically append the value from the column that you select for each row to the end of your "Relative URL" property.

    151561-image.png