Data unload from snowflake to Azure Blob using Data Factory

Question

Hi Team,

We are using Data factory to load the data from Snowflake into Azure blob storage in csv file format.

But we are getting error as mentioned below
ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [22000] Max file size (16777216) exceeded for unload single file mode. We recommend that you increase the max file size parameter, or disable single-file mode in the unload command and combine the unloaded files into a single file after you download them.,Source=Microsoft.DataTransfer.Runtime.GenericOdbcConnectors,''Type=System.Data.Odbc.OdbcException,Message=ERROR [22000] Max file size (16777216) exceeded for unload single file mode. We recommend that you increase the max file size parameter, or disable single-file mode in the unload command and combine the unloaded files into a single file after you download them.,Source=Snowflake,'

We could not able to find "SINGLE" property to disable it in Snowflake source section from Data factory and we cannot use MAX_FILE_SIZE because of huge file writing in 2gb.

We need to read the data from Snowflake single table into Azure blob storage with multiple file mode.

Could you please provide some information and help us to resolve this issue.

Thanks
Mukesh

Answer 1

Hi @Mukesh ,

Welcome to Microsoft Q&A forum and thanks for your query.

As per the error message it looks like a Max file size limitation issue.
In order to resolve this issue, it is recommend that you increase the max file size parameter, or disable single-file mode in the unload command and combine the
unloaded files into a single file after you download. Which is nothing but if you would like to go with SINGLE = FALSE then you will have to use staging in the sink side so that you can unload the data into multiple files in staging location and then you need to use another copy activity to copy/merge all of those unloaded files into a single file in desired destination/sink location.

Please note that when you use staging/SINGLE property as FALSE, don't give any file name in the sink dataset settings of your initial copy activity, so that copy activity will generate multiple files.

If you don't find the SINGLE to disable, please type in SINGLE as property name under Additional Snowflake copy options of copy activity source settings and set the value to FALSE as shown below.

Here is a relevant doc that could be helpful: https://docs.snowflake.com/en/sql-reference/sql/copy-into-location.html#copy-options-copyoptions

Hope this info helps. Do let us know if you have further query.

----------

Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.

Answer 2

I ran into the same error even if setting the parameter to SINGLE = FALSE.

I was able to solve it reviewing the source dataset. It was a parquet and I had an expression in the "filename" box, even if it was intended to be a directory path. I moved the expression to the directory box, left filename empty and was able to copy in multiple files (thus overcoming the single file limitations).