SQL Server Bulk Copy Program (BCP)

SQL Server Bulk Copy Program (BCP) utility (Bcp.exe) is a command-line tool that uses the Bulk Copy Program (BCP) to perform the following tasks:

  • The bcp utility can export data from a SQL Server table to a data file
  • The bcp utility can import data from a data file to a SQL Server table
  • Generates format files

The bcp utility is accessed by the bcp command or by using a batch file

You can use a batch file to set up a schedule using BCP

BCP is case sensitive

User must be aware of the data types and lengths of the table columns

The bcp command provides switches that you use to specify the data type of the data file and other information

SQL Server Bulk Copy Program

You will receive the number of rows copied, the network packet size and the speed to copy the rows per second.

If everything is OK, the file will be created:

You can open the file and check the data:

Bulk Copy Program

Syntax and switches

bcp [database_name.] schema.{table_name | view_name | "query" {in data_file | out data_file | queryout data_file | format nul}

   [-a packet_size]
   [-b batch_size]
   [-c]
   [-C { ACP | OEM | RAW | code_page } ]
   [-d database_name]
   [-e err_file]
   [-E]
   [-f format_file]
   [-F first_row]
   [-h"hint [,...n]"] 
   [-i input_file]
   [-k]
   [-K application_intent]
   [-L last_row]
   [-m max_errors]
   [-n]
   [-N]
   [-o output_file]
   [-P password]
   [-q]
   [-r row_term]
   [-R]
   [-S [server_name[\instance_name]]
   [-t field_term]
   [-T]
   [-U login_id]
   [-v]
   [-V (80 | 90 | 100 | 110)]
   [-w]
   [-x]
   /?

Examples

1. Using bcp to EXPORT data from SQL table to flat file using OUT

--Syntax: 

bcp databasename.schema.tablename out "to the path" -T (trusted credentials) -c (char datatype)

--the following command will export data FROM sql to flat file
bcp sql2.dbo.people3 out "c:\bcps\people3.txt" -T -c

--the following command will import data FROM flat file sql to
bcp sql2.dbo.people4 in "c:\bcps\people3.txt" -T -c

--create table peopel4 in SQL2 database to receive data from file

USE [SQL2]
GO

CREATE TABLE [dbo].[PEOPLE4](
	[PeopleID] [int] NOT NULL,
	[Fname] [varchar](20) NULL,
	[Lname] [varchar](20) NULL,
	[Address1] [varchar](100) NULL,
	[City] [varchar](50) NULL,
	[State] [varchar](50) NULL,
	[Zip] [varchar](10) NULL,
	[Country] [varchar](50) NULL,
	[Phone] [varchar](20) NULL
) ON [PRIMARY]

GO


--2. Send data from sql to file using a query

bcp "select top 100 * from sql2.dbo.people3" queryout "c:\bcps\query.txt" -T -c

Run bcp using PowerShell

PowerShell is a powerful tool to automate tasks using scripts and the command line. You can also run bcp using PowerShell, which is included with Windows and it can be also installed on Linux and Mac.

In PowerShell create variables to store the database name, schema, table and output path:

 $db = “adventureworks”$schema = “person”$table = “person”$path = “C:\sql\powershell.txt” 

Store the bcp command line with the parameters in another parameter:

 $psCommand = “bcp $($db).$($schema).$($table) out $path -T -c” 

Use the Invoke-Expression to call the variable with the bcp commands:

Invoke-Expression $psCommand 

PowerShell will import the files in the powershell.txt file:

Bulk Copy Program

Run bcp on SSIS

It is not a common practice to run bcp on SSIS because SSIS contains tasks to import and export data. You can use the Data Flow task to create customized tasks or maybe use the Bulk Insert task to import data to SQL Server.

However, there are some scenarios where you can invoke bcp in SSIS. For example, if you already have some command lines in bcp and you just want to invoke them. Another case is when you are adept at the command line and you already have some batch files ready and you just want to invoke them.

Open the SSDT and create a New Project.

In projects select Integration Services Project:

Drag and drop the Execute Process Task to the design pane:

In executable, specify the path of the bcp file:

C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\bcp.exe

In arguments specify the bcp arguments to export data from a SQL Server table to a text file named: ssis.bcp

adventureworks.person.person out c:\sql\ssis.bcp -c -T -S WIN-355CHQ0E524

Your SSIS tasks will look like this:

Right click the tasks and select Execute task:

If everything is OK, the task will be like this and a file named ssis.bcp will be created with the data exported: