Copy Data from REST API to Azure SQL DB with Data Factory

Question

Beginner here, I want to copy some stock data from a REST API into my Azure SQL database. I've set up a pipeline with a Look-up activity to get the needed strings (with stock symbols) and use them in For Each to call the API and copy the data to SQL database.

Problem: the copy activity works for some stock symbols, but not for all.

My Look-Up concatenates the stock symbols into a comma-separated string, for concatenating the string with the API call URL.

Here is the concatenate statement and the final output of the pipeline:

SELECT STRING_AGG(Symbol, ',') AS Symbollist
FROM [dbo].[Tab_Symbols]
GROUP BY GroupOfTen;

• green symbols --> appear in SQL database
• red symbols --> do not appear in SQL database

Why are the first and last symbols always missing?

I've tested the API call with the strings and I receive all stock symbols data in JSON format as it should. Maybe the problems is my mapping or sink SQL table?

Thank you! Fabian

Here are screenshots of my copy activity:

REST dataset settings and dynamic URL string:

For Each settings:

Source settings and use of dynamic string (use for each item):

Sink settings:

Mapping:

Answer 1

I solved it. In the source settings of my copy activity the column reference '.Symbollist' of the lookup-output was missing.

@item().Symbollist