Share via

Copy Activity Failing in Synapse for Flat File

MADHUSUDAN PANWAR 86 Reputation points
2022-10-28T12:47:21.687+00:00

Hi,

I'm trying to copy a flat csv file from ADLS2 to SQL table (dedicated sql pool) using COPY DATA Activity in Azure Synapse.
I have one source field in the format DD/MM/YYYY and during mapping it's showing warning as shown in pic1. 255152-image.png

I am able to see the data in preview source but during the actual run it's failing due to typeconversionerror. Can someone please tell me how to fix this? My trarget column is of 'DATE' datatype.
255087-image.png

255028-image.png

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,697 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,196 questions
Accepted answer
  1. KranthiPakala-MSFT 46,442 Reputation points Microsoft Employee
    2022-11-03T23:44:52.983+00:00

    Hello @MADHUSUDAN PANWAR ,

    Thanks for the question and using MS Q&A platform.

    Firstly, appreciate much for attaching the samples which helped to reproduce the issue.

    You were on spot and very close to resolve the issue. Please use dd/MM/yyyy in Type Conversion settings instead of DD/MM/YYYY which should help resolve the issue.

    257015-image.png

    I tried to reproduce the issue with the samples provided and issue resolved with above settings.

    257042-image.png

    Hope this helps. Please let us know if any further queries.

    ------------------------------

    • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ronen Ariely 15,186 Reputation points
    2022-10-29T08:15:19.363+00:00

    Hi,

    I am able to see the data in preview source

    Not accurate! In fact, this is the source of the confusion.

    What you see is a client side "interpretation" of the data. You see text and dates.

    The server is trying to CONVERT the text from the file into the right data type. In this case it try to CONVERT the text to a DATE, but since you did not inform the server what is the format of your information, it fails in the CONVERT. In other words, the server tried to guess what is the style of the format without success since it is not fit the default style.

    For more information regarding style of text which represent date, check this page:

    https://learn.microsoft.com/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16&WT.mc_id=DP-MVP-5001699#date-and-time-styles

    So... What is the solution

    You should inform the server what is your format using the parameter FILE_FORMAT and a file which include the format of your csv

    https://learn.microsoft.com/en-us/sql/t-sql/statements/copy-into-transact-sql?view=azure-sqldw-latest&WT.mc_id=DP-MVP-5001699#file_format--external_file_format_name

    The following doc can help you create the format file. Notice that you need to configure the parameter of DATE_FORMAT

    https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-file-format-transact-sql?view=azure-sqldw-latest&tabs=delimited&WT.mc_id=DP-MVP-5001699#date_format--datetime_format