How set parameters in SQL Server table from Copy Data Activity - Source: XML / Sink: SQL Server Table / Mapping: XML column

David Balderas 6

Hello,

I have a question, hopefully someone in the forum could give some help here. I am able to pull data from Soap API call to SQL Server table (xml data type field actually) via Copy Data Activity. The pipeline that runs this process is metadata driven, so how could I write other parameters in the same SQL Server table for the same run? I am using a Copy Data Activity to load XML data to SQL Server table but in Mapping tab I am not able to select other parameters in order to point them to others SQL table columns.

In addition, I am using a ForEach Activity in order the Copy Data Activity iterates for several values of one column on SQL Server table.

I will appreciate any advice on this.

Thanks
David

AnnuKumari-MSFT 30,601 Reputation points Microsoft Employee

2022-05-05T07:19:25.907+00:00
Hi @David Balderas ,
Welcome to Microsoft Q&A platform and thanks for posting your query.
As I understand your ask, you are trying to copy data from API to SQL table using copy data activity in Azure data factory pipeline.

However,

I would need clarification on whether you want to load data from multiple APIs to single table as different columns API1-> Table1, API2-> Table1 ?

Or you just want to parameterize the pipeline in such a way that same copy activity would help you load data as API1->Table1 , API2-> Table2. Please confirm on the same .

If the ask is to load from multiple APIs to single table, it would not be feasible idea if you want to maintain the foreign key constraint between the columns if any , meaning it will insert separate rows for different API. For example: Address1,Address2 coming from API1 and AssistantId,City coming from API2.

If the ask is to load from multiple APIs to multiple corresponding tables, then you can parameterize your dataset and pass the base URL of the API dynamically and create corresponding SQL tables with parameterized sink dataset. In that case, you don't need to import schema in copy activity mapping tab and select autocreate table option.

Please let me know if the suggested response helped.
David Balderas 6 Reputation points

2022-05-05T19:42:18.857+00:00

Thank you for your interest, I will try to be more explicit with this image: Hopefully this clarify a little bit. Given the current escenario, how could I pass StoreId and CustomerNumber parameters to the table Stage.XmlDataTable?

Taking in to account in the mapping step I am just able to map XML data from the current API call and then write it into Stage.XmlDataTable - XmlData column.

Thanks in advance David
AnnuKumari-MSFT 30,601 Reputation points Microsoft Employee

2022-05-12T18:19:07.487+00:00

Hi @David Balderas ,
Just following up to see if the above answer by @Ryan Abbey helped., kindly do consider clicking Accept Answer as accepted answers help community as well.

1 answer

Ryan Abbey 1,171 Reputation points

2022-05-05T20:39:52.203+00:00

copy-activity-overview

Sounds like that is what you're after
Please sign in to rate this answer.
David Balderas 6 Reputation points

2022-05-06T18:10:19.147+00:00

Correct RyanAbbey-0701, you saved my day..!!

AnnuKumari-MSFT 30,601 Reputation points Microsoft Employee

2022-05-09T05:18:11.483+00:00

Hi @David Balderas ,
Just checking in to see if the above answer by @Ryan Abbey helped., kindly do consider clicking Accept Answer as accepted answers help community as well.
Sign in to comment