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

Question

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

Partha Das 271

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

ShaikMaheer-MSFT 30,041 Microsoft Employee

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.

Partha Das 271 Reputation points

2022-09-02T09:31:54.007+00:00

Hi @ShaikMaheer-MSFT Your steps are clear enough to explain. But unfortunately, using your script, I can see results in SSMS but ADF doesn't return any value. Its still empty
ShaikMaheer-MSFT 30,041 Reputation points Microsoft Employee

2022-09-02T09:43:42.803+00:00

Hi @Partha Das , Looks strange. Kindly check all steps slow one by one in comparison with above screenshots. If still unable to find the root cause. Then mean while I would suggest to have a view created on table or Stored procedure for the task. Hope this helps.

----------

Please consider hitting Accept Answer button. Accepted answers help community as well.
Partha Das 271 Reputation points

2022-09-02T15:57:13.037+00:00

Hi @ShaikMaheer-MSFT it seems that since my xml is very large in size, ADF can't handle with Azure IR. It needs Self Hosted IR to work with huge size.

https://learn.microsoft.com/en-us/answers/questions/618115/unable-to-load-500-mb-xml-file-using-azure-data-fa.html

But marking your answer as correct.

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

0 additional answers

Activity