How to load the data from Sql server DB to Snowflake DB by ADF without using any external stage?

Question

How to load the data from Sql server DB to Snowflake DB by ADF without using any external stage?

Sahoo, Ajaya Kumar 0

We have PII data which is not approved by our security to stage in any cloud storage (ADLS)
Hence the requirement is to read the data from the source and load into the snowflake tables directly.
We don't need any staging layer like ADLS even for any temporary storage.
The ADF should copy the data directly into the Snowflake tables.

Krupal Bandari 665 Reputation points Microsoft External Staff Moderator

2025-06-12T08:48:30.9233333+00:00
Hi @Sahoo, Ajaya Kumar
In addition to Nandan Hegde
Yes, your requirement can be fully achieved using Azure Data Factory's direct copy method without using any external staging like ADLS or Blob Storage.

Since your data contains PII and cannot be temporarily stored in the cloud, you can configure ADF to directly copy data from SQL Server to Snowflake by disabling staging. This ensures that:

No temporary files are written to cloud storage.

Data is securely transferred via encrypted channels.

The solution is fully compliant with strict data privacy policies.

It is Microsoft supported and production ready.

Key configuration: In the ADF copy activity, make sure to set the “Use Staging” option to false in the Snowflake sink settings. This enables direct insert into Snowflake without any staging layer.

For your reference:

Snowflake connector in ADF – Write behavior

Copy Activity direct copy mode

This setup has been tested and confirmed to meet scenarios where security and compliance are critical, such as yours.

I hope this helps! Please let us know how it goes.

If you found this useful, consider upvoting the comment so it can help others in the community too.
Sahoo, Ajaya Kumar 0 Reputation points

2025-06-12T11:46:53.1366667+00:00

Thanks for the quick response!

We have both Database and Files to be loaded into Snowflake.

We are getting these errors:

"Direct copying data to Snowflake is only supported when source dataset is DelimitedText, Parquet, JSON with Azure Blob Storage or Amazon S3 linked service, for other dataset or linked service, please enable staging"
Krupal Bandari 665 Reputation points Microsoft External Staff Moderator

2025-06-13T01:33:30.12+00:00

Hi @Sahoo, Ajaya Kumar
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

2 answers

Your answer

Krupal Bandari 665 Reputation points Microsoft External Staff Moderator

2025-06-12T08:48:30.9233333+00:00

Hi @Sahoo, Ajaya Kumar
In addition to Nandan Hegde
Yes, your requirement can be fully achieved using Azure Data Factory's direct copy method without using any external staging like ADLS or Blob Storage.

Since your data contains PII and cannot be temporarily stored in the cloud, you can configure ADF to directly copy data from SQL Server to Snowflake by disabling staging. This ensures that:

No temporary files are written to cloud storage.

Data is securely transferred via encrypted channels.

The solution is fully compliant with strict data privacy policies.

It is Microsoft supported and production ready.

Key configuration: In the ADF copy activity, make sure to set the “Use Staging” option to false in the Snowflake sink settings. This enables direct insert into Snowflake without any staging layer.

For your reference:

Snowflake connector in ADF – Write behavior

Copy Activity direct copy mode

This setup has been tested and confirmed to meet scenarios where security and compliance are critical, such as yours.

I hope this helps! Please let us know how it goes.

If you found this useful, consider upvoting the comment so it can help others in the community too.
Sahoo, Ajaya Kumar 0 Reputation points

2025-06-12T11:46:53.1366667+00:00

Thanks for the quick response!

We have both Database and Files to be loaded into Snowflake.

We are getting these errors:

"Direct copying data to Snowflake is only supported when source dataset is DelimitedText, Parquet, JSON with Azure Blob Storage or Amazon S3 linked service, for other dataset or linked service, please enable staging"
Krupal Bandari 665 Reputation points Microsoft External Staff Moderator

2025-06-13T01:33:30.12+00:00

Hi @Sahoo, Ajaya Kumar
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Answer 1

Hi @Sahoo, Ajaya Kumar

Thanks for trying that out and sharing the exact error and you're absolutely right to flag it.

I’d like to clarify and correct my earlier response after reviewing your source setup. While ADF does support direct copy to Snowflake, this is only available when your source is a file-based dataset (DelimitedText, Parquet, or JSON) located in Azure Blob or Amazon S3.

However, for database sources like SQL Server, ADF requires a staging layer to temporarily move data before loading into Snowflake this is by design and currently enforced by the product. Even if the “Use Staging” option is unchecked, ADF will still throw the error you’re seeing.

Continue using direct copy for supported file-based sources

For PII data from SQL Server, staging is technically required in ADF. But depending on your organization’s compliance rules, you could explore:

Snowflake internal staging, which doesn’t use Azure Blob
Custom scripts using Snowflake’s connector (Python, .NET, JDBC) to securely stream from SQL Server without any external cloud staging

Reference:
https://learn.microsoft.com/en-us/azure/data-factory/connector-snowflake?tabs=data-factory#limitations

If this is helpful, please click Accept Answer and kindly upvote it so that other people who faces similar issue may get benefitted from it.

Let me know if you have any further Queries.

Answer 2

Nandan Hegde 35,831 MVP Volunteer Moderator

please refer the below link from MSFT :

https://learn.microsoft.com/en-us/azure/data-factory/connector-snowflake?tabs=data-factory#direct-copy-to-snowflake

Sahoo, Ajaya Kumar 0 Reputation points

2025-06-12T11:49:30.5033333+00:00

Thanks for the quick response!

We have both Database and Files to be loaded into Snowflake.

We are getting these errors if we dont select the "enable staging":

"Direct copying data to Snowflake is only supported when source dataset is DelimitedText, Parquet, JSON with Azure Blob Storage or Amazon S3 linked service, for other dataset or linked service, please enable staging"

Share via

How to load the data from Sql server DB to Snowflake DB by ADF without using any external stage?

2 answers

Your answer