I'm building my first Data Factory pipeline, a very basic one. I've a Data Flow with just source (csv flatfile) and sink (synapse table).
Source has 12 columns. So, I've created a table in Synapse (via SSMS) with all the 12 columns as varchar. No keys. Just a basic table. When I build the Data Flow activity, the previews of the data on both source and target looks perfect. But when I try to run (Debug) the pipeline, it just fails with the below error:
Operation on target load_sales_data failed: {"StatusCode":"DFExecutorUserError",
"Message":"at Sink 'Sales': java.sql.BatchUpdateException:
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]String or binary data would be truncated.
","Details":"at Sink 'Sales': java.sql.BatchUpdateException:
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]String or binary data would be truncated. "}
I just don't get it. I spent a lot of time trying to figure out what's wrong but I just don't get it. Can someone please tell me what am I doing wrong?
You have too short column length to fit data in csv column into database table. Check that you have specified suitable field lengths for your varchar columns. Note that by default length is one character long. The document for varchar data type say for varchar(n) that:
When n isn't specified in a data definition or variable declaration statement, the default length is 1.
If you have specified length, double check that the data in csv does not contain too long values.
Mismatch in field delimiter could cause ADF to treat whole row as value for first field and it would be longer than you expect. Check field delimiter setting for the csv source. You can preview the table data in Azure portal in ADF to validate that it see the csv structure correctly.
More info in Microsoft documents at https://learn.microsoft.com/en-us/sql/t-sql/data-types/char-and-varchar-transact-sql