question

DavidBalderas-7775 avatar image
0 Votes"
DavidBalderas-7775 asked AnnuKumari-MSFT commented

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

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

azure-data-factory
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @DavidBalderas-7775 ,
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,
1. I would need clarification on whether you want to load data from multiple APIs to single table as different columns API1-> Table1, API2-> Table1 ?
2. 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.

199084-image.png

  • 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.


0 Votes 0 ·
image.png (2.5 KiB)

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


199355-adf-diagram.png


0 Votes 0 ·
adf-diagram.png (115.0 KiB)

Hi @DavidBalderas-7775 ,
Just following up to see if the above answer by @RyanAbbey-0701 helped., kindly do consider clicking Accept Answer as accepted answers help community as well.

0 Votes 0 ·

1 Answer

RyanAbbey-0701 avatar image
0 Votes"
RyanAbbey-0701 answered AnnuKumari-MSFT commented

copy-activity-overview

Sounds like that is what you're after


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

1 Vote 1 ·

Hi @DavidBalderas-7775 ,
Just checking in to see if the above answer by @RyanAbbey-0701 helped., kindly do consider clicking Accept Answer as accepted answers help community as well.

0 Votes 0 ·