question

stevehawk-1987 avatar image
0 Votes"
stevehawk-1987 asked JeffBornJCLTATL-4260 edited

Copy Activity failing

Hi All i am new to ADF. I created a copy activity and it is dailing.
Source: ADL .csv file
Destination : SQLMI
My question is do i need to create a table , doesnt't COPY create it ?
Error Message
"errors": [
{
"Code": 11000,
"Message": "'Type=System.InvalidOperationException,Message=Cannot access destination table '[myschema].[REPORTS]'.,Source=System.Data,''Type=System.Data.SqlClient.SqlException,Message=Invalid object name 'Myschema.REPORTS'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=208,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=208,State=1,Message=Invalid object name 'MYSCHEMA.REPORTS'.,},],'",
"EventType": 0,
"Category": 5,
"Data": {},
"MsgId": null,
"ExceptionType": null,
"Source": null,
"StackTrace": null,
"InnerEventInfos": []
}
],
Please need help

azure-data-factory
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

KranthiPakala-MSFT avatar image
0 Votes"
KranthiPakala-MSFT answered

Hi @stevehawk-1987,

Welcome to Microsoft Q&A platform and thanks for using this forum.

ADF Copy activity has Auto create table feature supported when copying data from any source into the following sink data stores.

  • Azure SQL Database

  • Azure SQL Database Managed Instance

  • Azure Synapse Analytics (formerly Azure SQL Data Warehouse)

  • SQL Server

You can find the option on ADF authoring UI –> Copy activity sink –> Table option –> Auto create table

17236-image.png

Please enable Auto create table option in your Copy activity and rerun the pipeline. In case if you still encounter the issue even after enabling Auto create table option, please let us know.

Note: Documentation for - ADF Copy activity Auto create sink tables



Thank you
Please do consider to click on "Accept Answer" and "Upvote" on the post that helps you, as it can be beneficial to other community members.




image.png (16.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

stevehawk-1987 avatar image
0 Votes"
stevehawk-1987 answered KranthiPakala-MSFT commented

Thanks , i already did that. Please see the below screen shots, it still fails.
17158-image.png


17159-image.png



image.png (21.3 KiB)
image.png (22.3 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for your response @stevehawk-1987. This is strange, usually the error you have shared is thrown when Auto create Table option in Copy Activity is not enabled and the table name provided in the sink (Azure SQL) dataset is not present in the database.

Another limitation with Auto Create Table option is that it isn't supported when sink specifies stored procedure or copy activity is equipped with the staging settings. Could you please verify this?

Also could you please share below details to AzCommunity[at]microsoft[dot]com, for further investigation.

  • Subject : Attn - Kranthi : Microsoft Q&A Thread title

  • Thread URL: Microsoft Q&A thread URL

  • Subscription ID, Failed pipeline & activity runID?

  • Please attach downloaded ADF pipeline support files

  • Is this a trigger or debug run?


Let me know here once email is sent.







0 Votes 0 ·

Hi @stevehawk-1987,

Following up to see if you are still experiencing the issue? If so could you please share the details requested above for further investigation. In case if you have found a solution already, please do consider to share it here with community as it would be helpful for others reading this thread.

Thank you

0 Votes 0 ·

Hi @stevehawk-1987,

Just checking to see if your issue was resolved. In case if you still need assistance, please do let us know.

Thanks

0 Votes 0 ·
JeffBornJCLTATL-4260 avatar image
0 Votes"
JeffBornJCLTATL-4260 answered JeffBornJCLTATL-4260 edited

I am also experiencing this error while using a REST sink. Any suggestions on getting this to work would be great!

Also when I manually mapped the columns the Copy activity worked. I was hoping for automagically mappings, but maybe with a REST source to a SQL Sink I have to map the columns?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.