How to Load Local File to Azure Synapse using BCP?

Post author:Vithal S
Post last modified:March 9, 2021
Post category:Azure Synapse
Reading time:5 mins read

It is common practice to offload large table to a flat file such as CSV, TSV or fixed width files. Managing flat files such as CSV is easy and it is easy to transport using any electronic medium. In this article, we will check how to load or import local flat file to Azure Synapse using the BCP command with some examples.

Load Local Flat File to Azure Synapse using BCP Utility

The bulk copy program utility (bcp) bulk copies data from a file in a user-specified format to an instance such as Azure Synapse dedicated SQL pool. The bcp utility can be used to import large numbers of new rows into SQL Server or Azure synapse tables.

How to Install BCP Utility?

The command-line tools are General Availability (GA). You can download same from following link

Download the latest version of bcp Utility

Follow the on-screen instructions to install the BCP utility. It will install SQLCMD and BCP.

BCP Utility Syntax

Following is the syntax of BCP utility that you can use with Microsoft SQL Server and Azure Synapse Analytics data warehouse.

bcp TABLE_NAME in C:\path\file.csv -S Azure_synapse_server -d DATABASE -U USERNAME -P PASSWORD -q -c -t "DELIMETER" -e C:\path\error.log -r "ROW_DELIMIETR"

Execute following command on the command-line to check BCP utility options.

C:\Users\viths>bcp -help

Using BCP Utility to Load CSV File into Azure Synapse Table

For a simplicity we are loading readily available TPCDS call_center.dat flat file into Azure Synapse.

Assuming table is already available, following command will load the CSV flat file into Azure Synapse analytics data warehouse.

bcp TD.call_center in C:\Users\viths\Desktop\temp\call_center.dat -S sampleSynapse.database.windows.net -d sampleDB -U admin -P Password@123 -q -c -t "|" -e C:\Users\viths\Desktop\temp\error.txt -r "0x0a"

Sometimes, you may end up with BCP error such as “Cannot open server ‘sampleSynapse’ requested by the login. Client with IP address ‘100.200.97.87’ is not allowed to access the server.“. To resolve this issue, you must add your system IP to Azure synapse firewall IP list. Follow an official document.

Following is the BCP output after successfully loading a flat file into an Azure Synapse table.

C:\Users\viths>bcp TD.call_center in C:\Users\viths\Desktop\temp\call_center.dat -S sampleSynapse.database.windows.net -d sampleDB -U admin -P Password@123 -q -c -t "|" -e C:\Users\viths\Desktop\temp\error.txt -r "0x0a"

Starting copy...

6 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1125   Average : (5.33 rows per sec.)