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

Partha Das 286 Reputation points
2022-09-01T18:10:26.113+00:00

Hi,

I'm facing a strange problem with ADF.

237065-table.jpg

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
237027-script-activity.jpg

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

Azure SQL Database
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,711 questions
0 comments No comments
{count} votes

Accepted answer
  1. ShaikMaheer-MSFT 38,126 Reputation points Microsoft Employee
    2022-09-02T06:14:17.723+00:00

    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:
    237119-image.png

    Script Activity Execution in ADF:
    237120-image.png

    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.


0 additional answers

Sort by: Most helpful