Same SQL Source table - ADF Copy Activity Succeeds but Fabric Copy Activity Fails- Auto create table

[AJAJ]

Hi there,

I'm using dynamic query to loop thru processing multiple tables. Source is SQL Server.

Same table / Same query / Same SQL Source - Older ADF copy activity runs successfully.

Fabric copy activity errors out - Something is wrong. Production also has the column delivery_instructions1 as varchar(20) and runs fine. Not sure why fabric is crying.

Fabric pipeline execution failed, error message received from failing pipeline - 'Operation on target CopySourceData failed: ErrorCode=DWCopyCommandOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message='DataWarehouse' Copy Command operation failed with error 'String or binary data would be truncated while reading column of type 'VARCHAR(20)'. Check ANSI_WARNINGS option. Underlying data description: file 'https://olsby4qhfhvxxxdfs.core.windows.net/e6913139-a2f675-d8df0be00770/da590d8b-xxxx9703-8e1f4e17e2...', column 'delivery_instructions1'. Truncated value: 'Contact: Jorge – 8'.
Statement ID: {4AFB2C23-E271-xx6A8B15EA8} | Query hash: 0xA4B38D547F0BF881 | Distributed request ID: {255xxx0-44A1-A86B-DA03E89D4C1C}'.,Source=Microsoft.DataTransfer.Connectors.MSSQLImport,''Type=Microsoft.Data.SqlClient.SqlException,Message=String or binary data would be truncated while reading column of type 'VARCHAR(20)'. Check ANSI_WARNINGS option. Underlying data description: file 'https://olsby4qhfhxxxxuzhk.dfs.core.windows.net/e6913139xxxxe-9595-d8df0be00770/da590d8b-cdf5-426a-9...', column 'delivery_instructions1'. Truncated value: 'Contact: Jorge – 8'.
Statement ID: {4AFB2C23-E271xxxxx6A8B15EA8} | Query hash: 0xA4B38D547F0BF881 | Distributed request ID: {255787xxxx0-44A1-A86B-DxxxxC1C},Source=Framework Microsoft SqlClient Data Provider,''

[v-prasare]

Hi @AJAJ,

We are following up once again regarding your query. Could you please confirm if the issue has been resolved through the support ticket with Microsoft?
If the issue has been resolved, we kindly request you to share the resolution or key insights here to help others in the community. If we don’t hear back, we’ll go ahead and close this thread.
Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

Thank you for your understanding and participation.

Prashanth

MS Fabric community support

[AJAJ]

I agree @tayloramy , i got access to source SQL server and narrowed down on table.

Copy Activity Error Column 'delivery_instructions1'. Truncated value: 'Contact: Jorge – 8'. 

deliver_instruction1 at source SQL table is varchar(20). I compared table structure side by side (SQL Server vs WH) and they are identical but fails with above error.

Value is 'Contact: Jorge – 832' - varchar(20). 

All checks out fine. Not sure why its reporting about issues. when everything is to the dot. There are other tables running fine and few tables are having issues. Out 4 tables out of 93 ingestion tables. and have no idea why.

There are some 45 columns in total on this 1 table.

[tayloramy]

Hi @AJAJ, 

Interesting. I wonder if that em-dash is taking up more bytes in the fabric implementation. 

If you set the target to be a varchar(25) or 30, does it work then? 

If not. I think a ticket with mirosoft would be helpful here. Something is up.  

[AJAJ]

Yes when i manually widended the target table column and it ran fine. Cant be declaring source table structures manually (each table has many columns so even if one column is casted to varchar greater than source, another column creates issue.) copy activity should autodetect especially when source is a SQL server. 

Something is a bug. i wish someone from microsoft could take a look. I used trial version and paid version too. Both have same issue. I have worked on Azure copy activity for 5 yrs using various sources. Never came across this issue. i created this fabric pipeline from scratch multipletimes.. 

95 tables to be processed - lookup and foreach loop thru with appropriate select statement (eg select col1, col2 from tableA and so on)  on copy activity.

18 tables are causing similar error. 

Verified len of Source SQL server tables and values. Fabric is turning out to be a crapy show here.

Same thing with reading pipedelimited csv files. Some files are fine but are having issues with copy activity while sames files runs smoothly in ADF.

[tayloramy]

Hi @AJAJ, 

I recommend you open a ticket with Microsoft. 

Automatic mapping tools have come a long way, but they are not 100% reliable yet.  

What you can do is in the source query, you can write a select statement that will cast the columns to a larger datatype, and then the copy data activity mapping will pick it up. 

[v-prasare]

As @tayloramy  suggested to raise a support ticket for Fabric and Power BI, kindly follow the steps outlined in the following guide:

How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

[AJAJ]

@tayloramy Thanks. Is this a paid support? Is there a microsoft bug reporting place where we can share any specific GUIDs that they can trace and fix. 

[v-prasare]

Hi @AJAJ,

No this is not paid support though you need a powerbi subscription to raise support ticket and do include more information like failure details like error message, activity Id, capacity and other detail necessary to track and provide resolution to your case

Thanks,

Prashanth 

MS Fabric community support

[AJAJ]

Thanks @v-prasare . How to proceed here. We do have pbi pro across the company but this is fabric trial within that pbi pro.

[v-prasare]

Hi @AJAJ,

start a new support request by clicking New support request (or Create support ticket). You will then be prompted to choose a Support Plan. On this screen, click Select or add contract. If Microsoft Fabric Subscription is available, select it.

If no contract appears, click + Add new contract, sign in using the Azure subscription owner or global admin account, and ensure that the Fabric capacity is linked to an active Azure subscription.

After selecting the support plan, fill in the ticket details by choosing the appropriate Problem type (such as Fabric, Power BI, or Data Engineering) and providing a clear Description of the issue. Attach any relevant screenshots or error messages to help the support team investigate.

Thanks,

Prashanth

MS fabric community support

[v-prasare]

Hi @AJAJ,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

@tayloramy  ,Thanks for your prompt response

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[tayloramy]

Hi @AJAJ, 

The error is pretty clear: String or binary data would be truncated while reading column of type 'VARCHAR(20)'

You have an issue with column mappings, and data is being truncated. 

You need to adjust the column mappings to ensure that data is not getting truncated. 

[AJAJ]

@tayloramy Thanks Taylor. Like i said, same identical pipeline is running fine in existing ADF (same source, same table, same everything) but few tables is not working in fabric. Nothing hardcoded in column mapping. multiple tables get processed by 1 pipeline. Fabric has processed other tables successfully. Data values are not exceeding.

The error is not correct. What i mean is if you connec the same table via ADF it runs fine but not in fabric. no mapping since many tables are processed by 1 pipeline. am i missing anything?

[tayloramy]

Hi @AJAJ, 

Azure Data Factory may just truncate the data without throwing an error. Fabric Data Factory does not, it will fail instead of allowing the data to truncate. 

[AJAJ]

@tayloramy 

Interesting. I dont have access to source at this point and working on getting access.

When source is SQL Server and has table structure as varchar(100), will fabric pick up same column as with shorter length? and assign varchar(20) while source has 100? so data is coming with more length? (i understand if its other platform, copy activity may assign varchar(max) source SQL server is microsofts own product) And i dont think currently successfully running ADF is truncating data. Never came across data truncation in past 5+yrs of work in azure adf copy activity.  Its weird since ADF copy activity and fabric copy activity point to the same source table. Few tables have the same error while its running fine on ADF Copy activity. 

At this point im waiting to get access to source to compare. 

[tayloramy]

Hi @AJAJ, 

In my experience, Fabnric is pretty good at auto determining the size of columns from SQL Server. I have seen problems when Fabric is selecting from a view, both on SQL Server and Oracle, but with a table I have never had these problems. 

What does your set up look like?