在Azure Data Factory 2中遍历Azure SQL表调用REST API遇阻求助

Hey Steve, glad you got the first parts sorted (Blob to SQL and hardcoded API calls)! Let's get that For Each loop working to iterate over your SQL table rows and send each one via the PUT API. Here's a step-by-step breakdown tailored for ADF beginners:

1. First, Fetch Your SQL Table Rows with a Lookup Activity #

The For Each activity needs an array of data to iterate over—and you can't directly point it at your SQL table. So first, add a Lookup activity to your pipeline:

• Set its source to your Azure SQL table dataset.
• Go to the Settings tab and uncheck First row only (this makes it return all rows instead of just one).
• Run the Lookup activity alone first to verify it outputs an array of your table rows (check the Output tab after debugging).

2. Configure the For Each Activity #

Next, add your For Each activity and link it after the Lookup:

• In the Items field, use this dynamic content to pass the Lookup's output array:

  @activity('Lookup SQL Table').output.value
  

• If your API has rate limits or you want to debug one row at a time, set Sequential to On under the Settings tab. Otherwise, leave it as parallel (adjust the Batch count if needed).

3. Wire Up the Web Activity Inside For Each #

Now add the Web activity inside the For Each container:

• Set the Method to PUT and enter your API URL.
• For the Body (the data sent to the API), use dynamic content to reference the current row's columns. For example, if your SQL table has columns customer_id, email, and subscription_status, your JSON body would look like:

  {
    "customerId": "@item().customer_id",
    "emailAddress": "@item().email",
    "status": "@item().subscription_status"
  }
  

  • Replace the column names (like customer_id) with your actual SQL column names.
  • Make sure the JSON structure matches what your API expects—double-check commas and quotes!
• If your API requires authentication (e.g., a Bearer token), add it to the Headers section. For example:

  Authorization: Bearer @variables('YourAccessTokenVariable')
  

4. Debug & Troubleshoot Common Issues #

• Test the Lookup first: Confirm its output has the correct rows by checking the debug logs. If it's empty, double-check your SQL dataset connection and table name.
• Verify row data in the loop: Add a Set Variable activity inside the For Each (before the Web activity) to store @item() in a string variable. Run the pipeline and check the variable's value to ensure each row is being picked up correctly.
• Check API errors: If the Web activity fails, look at the Response and Error tabs in the debug logs. Common issues include malformed JSON (missing quotes, wrong column names) or authentication problems.
• Handle data formatting: If your API expects specific formats (e.g., dates), use ADF functions to convert values. For example:

  "@formatDateTime(item().signup_date, 'yyyy-MM-ddTHH:mm:ssZ')"
  

That should get your loop up and running! Start with a small test dataset to debug faster, then scale up.

内容的提问来源于stack exchange，提问作者steve davidson