Copy Data Activity where the sink is a View RRS feed

  • Question

  • I am deploying an Azure Data Factory pipeline that contains a Copy Data activity, where the sink is a SQL Server View with trigger instead of insert. 

    The Pipeline run gives me the error:

    Activity Plants failed: 'Type=System.Data.SqlClient.SqlException,Message=View or function 'dbo.View_xxxxx' is not updatable because the modification affects multiple base tables.,Source=.Net SqlClient Data Provider,SqlErrorNumber=4405,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=4405,State=1,Message=View or function 'dbo.View_xxxxx' is not updatable because the modification affects multiple base tables.,},],'

    • Edited by Fabrizio O Tuesday, September 3, 2019 2:13 PM
    Tuesday, September 3, 2019 2:11 PM

All replies

  • Hello Fabrizio , 

    I just wanted to know as to how are you setting the view name , probably updating the ARM template , as when i see the ADF UI , i only see the Table option (snapshot below) 

    Anyways going back to the error . I am aware that we can insert into the base table referencing view and not the base table something like 

    INSERT INTO  SomeView values (....) in lieu of 

    INSERT INTO  SomeTable  values (....) 

    Where view is defined as

    CREATE VIEW  SomeView AS SELECT * FROM SomeTable  

    But i am sure that the view which you are trying to use is referencing multiple base tables . I suggest please take a look at the view definition . 

    Please do let us know how it goes .

    Thanks Himanshu

    Tuesday, September 3, 2019 9:59 PM
  • Hello Himanshu,

    The user interface (ADF) displays both tables and views.

    My view refers to multiple tables 

    SqlServer involves writing in a view with multiple tables via a trigger instead of insert.

    I don't understand why ADF does not support a standard SqlServer feature

    Thanks Fabrizio

    • Edited by Fabrizio O Wednesday, September 4, 2019 8:08 AM
    Wednesday, September 4, 2019 8:08 AM
  • Hello ,

    I would suggest you to share this ask https://feedback.azure.com/forums/270578-data-factory

    All of the feedback you share in these forums will be monitored and reviewed by the Microsoft engineering teams responsible for building Azure.

    Thanks Himanshu

    Wednesday, September 11, 2019 8:06 PM