File is not readed completely by Copy Data in Azure Data Factory

Question

I'm developing a pipeline that be able to insert data from a .txt file located in the Blob Storage into a table in a SQL Data Base.

Problem: Somehow the activity configuration is not working properly cause' is not reading all the records in the file and in consequence is not loading all the data into the Data Base (I realized this issue when I opened the file and compared the number of records from .text file against SQL table. Also, when I searched records from the last month in the table on SQL I didn't find them)

Answer 1

As discussed in comments, while using copy activity you would have to make sure to set the schema before running the activity. By design the schema mapping is left empty and has to be configured by the user either manually or asking adf to import the schema from the dataset.

Note: While using Auto create table option in sink, it automatically creates sink table (if nonexistent) in source schema, but won't be supported when a stored procedure is specified (on the sink side) or when staging is enabled.

Using COPY statement to load data into Azure Synapse Analytics as sink, the connector supports automatically creating destination table with DISTRIBUTION = ROUND_ROBIN if not exists based on the source schema.

Refer official doc: Copy and transform data in Azure Synapse Analytics by using Azure Data Factory or Synapse pipelines

Source...

Sink...

So Azure Synapse will be used as the sink. Additionally, an Azure Synapse table has to be created which matches the column names, column order, and column data types of source.

For dynamic mapping

If you view the pipeline code, you can see in the Translator section the JSON equivalent of the mapping section from UI.

You can reuse this as a base in Dynamic mapping to enable further copying similar files without having to manually configure schema.

Copy the JSON under mappings in translator