Azure Data Factory can't handle table named "Module"

Question

Hi,

I'm facing a strange problem with ADF.

I've a Azure SQL table named Module

I have a script activity which will output the table data in XML format and finally convert to string

I'm not getting any output but if I rename the table name as Module1 and change the script accordingly, it is returning the values.

Seems ADF is considering 'Module' as any command.
I can't change the DB table name. How I can handle this situation in ADF?

Early response is highly appreciated.

Regards,
Partha

Answer 1

Hi @Partha Das ,

Thank you for posting query in Microsoft Q&A Platform.

I tried to reproduce your case. Its working fine with table names as dbo.Module. Kindly check below screenshots and script and try to implement in same way.

Execution in SSMS:

Script Activity Execution in ADF:

Script Used in Script Activity:

DECLARE @xml XML;  
DECLARE @stringXML varchar(max);  
  
SET @xml = (SELECT * FROM dbo.Module FOR XML PATH('Module'),TYPE);  
SET @stringXML = CONVERT(varchar(max),@xml);  
  
SELECT @stringXML as stringXML;  

For Some reason, If still you are facing issue, you can consider having a view on top of table and then write script to create XML from view or you can also consider having stored procedure.

Hope this helps. Please let us know if any further queries.

--------------

Please consider hitting Accept Answer button. Accepted Answers help community as well.