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

Question

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

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

Answer 1

You can add your parameters using Additional Columns in the Copy data activity Source.

When you import schema in mapping you can see the additional columns added in source.

Refer to this MS document for more details on adding additional columns during the copy.